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
 General SQL Server Forums
 New to SQL Server Programming
 Query help -single column data with _

Author  Topic 

nikku
Starting Member

1 Post

Posted - 2013-01-24 : 18:00:16
I have column data as ex: Test_id = abcd_1234_567

Now I need to retrive only '1234' from the Test_id.How can we get only the data inbetween '_' ?

Can any one help with the SQL statement. only select can be used to retrive.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-24 : 18:58:23
What do you mean only SELECT can be used? Anyway, select PARSENAME(REPLACE(Test_ID,'_','.'),2), but you'll still need a FROM clause.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 00:42:05
also your format has to be consistent if you use PARSENAME otherways results can be unexpected

for example if you've just abcd_1234 it wont work

if you can atleast guarantee that you want part from first _ then you can do this

SELECT LEFT(STUFF(field,1,CHARINDEX('_',field),''),CHARINDEX('_',STUFF(field,1,CHARINDEX('_',field),'')+'_')-1)
FROM table


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -