Query for separate numbers from the string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Query for separate numbers from the string

Author

Topic

king_fisher
Starting Member

13 Posts

Posted - 2013-12-18 : 08:31:25

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 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

it shows the error

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

vijay nelson

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-12-18 : 08:37:08

Sorry but I didn't get you.

Can you please post the wanted result in relation to the given examples?

Too old to Rock'n'Roll too young to die.

king_fisher
Starting Member

13 Posts

Posted - 2013-12-18 : 23:43:14

quote:
Originally posted by webfred

Sorry but I didn't get you.

Can you please post the wanted result in relation to the given examples?

Too old to Rock'n'Roll too young to die.

i wanna get the name only,thanks

and i solved this by myself

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

vijay nelson

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-19 : 03:33:03

[code]
SELECT LEFT(name,CHARINDEX('(',name + '(')-1)
FROM Table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

king_fisher
Starting Member

13 Posts

Posted - 2013-12-26 : 23:37:04

quote:
Originally posted by visakh16

SELECT LEFT(name,CHARINDEX('(',name + '(')-1)
FROM Table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

good one

vijay nelson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources