extract the id's only

Question

Post reply

extract the id's only

kiran.rajenimbalkar

SSCrazy

Points: 2603
More actions
November 27, 2013 at 11:36 pm

#300886

Hi Expert,
CREATE TABLE tmp_id
(Name nvarchar(100) null)
INSERT INTO tmp_id VALUES ('King, Martin F. [1038134]')
INSERT INTO tmp_id VALUES ('Abcdef-Hh, Cccc Dddd Eeee [6998093]')
INSERT INTO tmp_id VALUES ('Height, Right C. [5843105]')
INSERT INTO tmp_id VALUES ('Diver, Jaam E. [6916417]')
INSERT INTO tmp_id VALUES ('Jtrettt, Mmmmmm [8111145]')
I wnat extract the id's only
like
1038134
6998093
5843105
6916417
8111145

Viewing 6 posts - 1 through 5 (of 5 total)

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

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 1

Hi,

Imho you can extract in such way.

select SUBSTRING(Name,CHARINDEX('[',Name)+1,CHARINDEX(']',Name)-(CHARINDEX('[',Name)+1)) as result

from tmp_id

Regards

Mike

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 2

Hi Expert,

thanks for your help.

Its worked for above sql.

But same logic I have used in my SQL,its giving me beloe error

"Invalid length parameter passed to the LEFT or SUBSTRING function"

michal.lisinski SSCertifiable Points: 6933 More actions · Answer 3

Please check more rows, probably there's no opening or/and closing bracket in one or few rows.

select Name

from tmp_id

WHERE CHARINDEX('[',Name) = 0 OR CHARINDEX(']',Name) =0

Regards

Mike

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

This is pretty simple to do with a pattern-based string splitter like the one in the 4th article in my signature links:

SELECT Item

FROM tmp_id

CROSS APPLY PatternSplitCM(Name, '[0-9]')

WHERE [Matched]=1;

If your names can contain numbers, you may need to make some adjustments to the pattern [0-9] you pass or the filtering.

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

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

REPLACE(STUFF(Name,1,CHARINDEX('[',Name+'['),''),']','')

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