查询字符串中的单独数字
问题描述:
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.