Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extract string Expand / Collapse
Author
Message
Posted Tuesday, November 26, 2013 6:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:14 AM
Points: 1,870, Visits: 2,276
Hi all,

I have string like 'ABCD_kkk_DDD'

I want only 'kkk' in output. How to achieve this?



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1517661
Posted Tuesday, November 26, 2013 7:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 6,811, Visits: 6,306
One way

SUBSTRING(@string,CHARINDEX('_',@string)+1,CHARINDEX('_',SUBSTRING(@String,CHARINDEX('_',@string)+1,255))-1)



Far away is close at hand in the images of elsewhere.

Anon.

Post #1517663
Posted Tuesday, November 26, 2013 7:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:14 AM
Points: 1,870, Visits: 2,276
David Burrows (11/26/2013)
One way

SUBSTRING(@string,CHARINDEX('_',@string)+1,CHARINDEX('_',SUBSTRING(@String,CHARINDEX('_',@string)+1,255))-1)

Thanks David,
I have implemented in the same manner..but I am looking for another solutions for this



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1517665
Posted Tuesday, November 26, 2013 7:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 6,811, Visits: 6,306
Why another>
Is there a special requirement?

You could use a splitter, eg DelimitedSplit8K (search this site).
Although this seems overkill for this.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1517674
Posted Tuesday, November 26, 2013 7:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 12,749, Visits: 31,115
ok, a kewl sneaky way taking advantage of parsename
 Declare @TableName varchar(128) ='ABCD_kkk_DDD'
SELECT PARSENAME(REPLACE(@TableName,'_','.'),2)

and the long drawn out way:

Declare @TableName varchar(128) ='ABCD_kkk_DDD'
SELECT SUBSTRING(@TableName,0,CHARINDEX('_',@TableName)) As Pt1,
REPLACE(@TableName,SUBSTRING(@TableName,0,CHARINDEX('_',@TableName))+'_','') As Inprogress,
SUBSTRING(REPLACE(@TableName,SUBSTRING(@TableName,0,CHARINDEX('_',@TableName))+'_',''),0,CHARINDEX('_',REPLACE(@TableName,SUBSTRING(@TableName,0,CHARINDEX('_',@TableName))+'_','')))
As Part2



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1517677
Posted Tuesday, November 26, 2013 7:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:46 AM
Points: 6,811, Visits: 6,306
Lowell (11/26/2013)
ok, a kewl sneaky way taking advantage of parsename
 Declare @TableName varchar(128) ='ABCD_kkk_DDD'
SELECT PARSENAME(REPLACE(@TableName,'_','.'),2)



As long as the string does not already contain full stops



Far away is close at hand in the images of elsewhere.

Anon.

Post #1517698
Posted Tuesday, November 26, 2013 11:36 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:05 PM
Points: 825, Visits: 2,495
This is much the same as the the substring option, but uses stuff
 SELECT 
STUFF(
STUFF(
@TableName
,1
,CHARINDEX('_',@TableName),''
) -- Remove up to first
,CHARINDEX('_',@Tablename) - 1
,9999
,''
) --Remove from second

Post #1517780
Posted Tuesday, November 26, 2013 5:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:42 PM
Points: 3,596, Visits: 5,112
Need my morning coffee.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1517860
Posted Tuesday, November 26, 2013 9:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:45 PM
Points: 36,013, Visits: 30,300
kapil_kk (11/26/2013)
David Burrows (11/26/2013)
One way

SUBSTRING(@string,CHARINDEX('_',@string)+1,CHARINDEX('_',SUBSTRING(@String,CHARINDEX('_',@string)+1,255))-1)

Thanks David,
I have implemented in the same manner..but I am looking for another solutions for this


Ok... your turn. Why did you need a manner different than what David offered?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1517882
Posted Wednesday, November 27, 2013 4:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 2:23 AM
Points: 63, Visits: 368
I think you'll find that the tsql solution for resolving your issue is pretty much along the lines you have been shown. If you'd like alternatives, maybe the popular splitter function can be of help or if you like you can do your string handling within the confines of a CLR Procedure/Function.

For instance, a C# expression to achieve what you want might go something like this:

"ABCD_kkk_DDD".Split('_').GetValue(1)

Post #1517999
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse