Extract string

Question

Post reply

Extract string

kapil_kk

SSC-Insane

Points: 21316
More actions
November 26, 2013 at 6:52 am

#395897

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/

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65144 More actions · Answer 1

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.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

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/

David Burrows SSC Guru Points: 65144 More actions · Answer 3

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.

Lowell SSC Guru Points: 323518 More actions · Answer 4

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Burrows SSC Guru Points: 65144 More actions · Answer 5

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.

mickyT SSChampion Points: 10360 More actions · Answer 6

This is much the same as the the substring option, but uses stuff

SELECT

STUFF(

@TableName

,1

,CHARINDEX('_',@TableName),''

) -- Remove up to first

,CHARINDEX('_',@Tablename) - 1

,9999

,''

) --Remove from second

Dwain Camps SSC Guru Points: 86978 More actions · Answer 7

Need my morning coffee.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004471 More actions · Answer 8

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CKX SSCrazy Points: 2519 More actions · Answer 9

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)