SQL Query to Extract after 2nd - in a field

Question

SQL Query to Extract after 2nd - in a field

trvlbabie

Old Hand

Points: 328
More actions
February 16, 2016 at 10:56 am

#316035

I am trying to extract the 5-digit number out of a field that is formatted in the following manner:
XXXX-###-[highlight="#ffff11"]#####[/highlight]-#########-#
I need to extract the 5-digit number (highlighted) after the second '-' . I'd like to use SUBSTRING if possible in SQL. Help???

Viewing 13 posts - 1 through 13 (of 13 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You have several options. You can play with substring and charindex if the first items have variable lengths, or you could split the whole string and take the part you need.

For the splitter, check this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

SELECT SUBSTRING( String, CHARINDEX( '-', String + '--', CHARINDEX( '-', String + '-') + 1) + 1, 5), --If the first 2 items have variable lengths

SUBSTRING( String, 10, 5) --If it's a fixed position

FROM (VALUES('XXXX-###-#####-#########-#'))x(String)

SELECT Item

FROM (VALUES('XXXX-###-#####-#########-#'))x(String)

CROSS APPLY dbo.DelimitedSplit8K( String, '-')

WHERE ItemNumber = 3

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

trvlbabie Old Hand Points: 328 More actions · Answer 2

When I add the line: SUBSTRING(String, 10, 5)

It is giving me the following error: SQL API: [SQLExecDirectW], SQL RETURN: [-1], SQL STATE: [42000], SQL NATIVE ERROR: [-131], SQL MESSAGE: [[Sybase][ODBC Driver][Sybase lQ] Syntax error near ')' on line 27]

Any ideas?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

trvlbabie (2/16/2016)
When I add the line: SUBSTRING(String, 10, 5)
It is giving me the following error: SQL API: [SQLExecDirectW], SQL RETURN: [-1], SQL STATE: [42000], SQL NATIVE ERROR: [-131], SQL MESSAGE: [[Sybase][ODBC Driver][Sybase lQ] Syntax error near ')' on line 27]
Any ideas?

To fix that error, go to a Sybase forum. The syntax that I showed is only guaranteed to work on SQL Server, because this is a SQL Server forum.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

trvlbabie Old Hand Points: 328 More actions · Answer 4

I'm new to this so I wasn't sure - appreciate that you were so patient and helpful...not.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

trvlbabie (2/16/2016)
I'm new to this so I wasn't sure - appreciate that you were so patient and helpful...not.

I'll give you some advice since you say that your new to this. Pay attention to what you're doing. You wouldn't take a Corolla to a Volkswagen repair shop just because it is your first car.

I could have searched the syntax for the substring function in sybase, but it's not my job. I'm not getting paid for this and most people helping on forums aren't being paid either. So try to keep your immature responses for yourself.

If you have problems, the first thing you should do is to read the manual, then ask a co-worker and then ask online.

The logic is there, if you try to understand the code, you can fix it. If you don't understand it, don't use it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

autoexcrement SSCertifiable Points: 5956 More actions · Answer 6

@trvlbabie, he WAS patient and helpful. what an ungrateful and rude person you are. luckily people like you are very rare on these forums.

"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

trvlbabie (2/16/2016)
I'm new to this so I wasn't sure - appreciate that you were so patient and helpful...not.

You must like pork chops. 🙂

The following will help. http://bfy.tw/4IPS

--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)

SQLRNNR SSC Guru Points: 281344 More actions · Answer 8

Jeff Moden (2/16/2016)
trvlbabie (2/16/2016)
I'm new to this so I wasn't sure - appreciate that you were so patient and helpful...not.
You must like pork chops. 🙂
The following will help. http://bfy.tw/4IPS

hahahahah - internet points to you for the day.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Alan Burstein SSC Guru Points: 61142 More actions · Answer 9

and most people helping on forums aren't being paid

Huh?!?! I just thought my SSC paycheck was really late. Aw man!

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

Alan.B (2/16/2016)
and most people helping on forums aren't being paid
Huh?!?! I just thought my SSC paycheck was really late. Aw man!

Maybe you haven't done the correct posts. I've just been paid for two of them.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61142 More actions · Answer 11

trvlbabie (2/16/2016)
When I add the line: SUBSTRING(String, 10, 5)
It is giving me the following error: SQL API: [SQLExecDirectW], SQL RETURN: [-1], SQL STATE: [42000], SQL NATIVE ERROR: [-131], SQL MESSAGE: [[Sybase][ODBC Driver][Sybase lQ] Syntax error near ')' on line 27]
Any ideas?

I suspect that us is because you can't use the VALUES constructor like Luis did using sybase. If what you are looking for however is the 10th through 15th characters you can use the SUBSTRING function ad Luis demonstrated. Otherwise you can work it out using Sybase's CHARINDEX or PATINDEX functions.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

MMartin1 One Orange Chip Points: 27851 More actions · Answer 12

Jeff Moden (2/16/2016)
trvlbabie (2/16/2016)
I'm new to this so I wasn't sure - appreciate that you were so patient and helpful...not.
You must like pork chops. 🙂
The following will help. http://bfy.tw/4IPS

I think this is the best thing I can take away from this thread, I have discovered "lmgtfy"

----------------------------------------------------