SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query help -single column data with _
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 01/24/2013 :  18:00:16  Show Profile  Reply with Quote
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.

Edited by - nikku on 01/24/2013 18:17:13

Flowing Fount of Yak Knowledge

2872 Posts

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


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

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 01/25/2013 :  00:42:05  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000