SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
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  

nikku
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

jimf
Flowing Fount of Yak Knowledge

USA
2868 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.

Jim

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

visakh16
Very Important crosS Applying yaK Herder

India
47099 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
http://visakhm.blogspot.com/
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.05 seconds. Powered By: Snitz Forums 2000