查询字符串中的单独数字 [英] Query for separate numbers from the string

问题描述

id  name

1   Ajil S (C1185)
2   Ajil S (C1185)
3   Joe P (C1157)
4   Joe(A1160)
5   Raj (A1174)
6   Sreemathi (A1190)
7   Nadarajan P (C1187)
8   Sreemathi S (C1230)
9   Rajakumaran C (C1192)

i的名字就像这个名字（5分代码）



如何从这个列中获取名称，这是我的查询

i have a name like this "name"(5 degits of code)

how to get the name from this column,this is my query

select left(name,(len(name)-7)) from tbl_payment_fake

它显示错误r $>

it shows the error

Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

推荐答案

有可能你的数据不好：尝试运行一个简单的查询：

Chances are that you have bad data in there: try running a simple query:

SELECT id, name FROM tbl_payment_fake WHERE LEN(Name) < 8

并看看你得到了什么 - 如果你有任何你可能需要先修复它们。

and see what you get - if you have any you may need to fix them first.

测试它：

Test it:

DECLARE @tmp TABLE ([Name] VARCHAR(30))

INSERT INTO @tmp ([Name])
VALUES('Ajil S (C1185)'),
('Ajil S (C1185)'),
('Joe P (C1157)'),
('Joe(A1160)'),
('Raj (A1174)'),
('Sreemathi (A1190)'),
('Nadarajan P (C1187)'),
('Sreemathi S (C1230)'),
('Rajakumaran C (C1192)')


SELECT RTRIM(LEFT([Name],CHARINDEX('(',[Name])-1)) AS NewName
FROM @tmp

结果：

Result:

Ajil S 
Ajil S 
Joe P 
Joe
Raj 
Sreemathi 
Nadarajan P 
Sreemathi S 
Rajakumaran C 

select reverse(substring(reverse(name),8,100)) from tbl_payment_fake

工作。

its working.

这篇关于查询字符串中的单独数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！