Substring from RIGHT

Question

Substring from RIGHT

sharonsql2013

SSCertifiable

Points: 5908
More actions
March 4, 2016 at 9:43 am

#309170

I am trying to get a substring of characters but need to go from right
Example 9.0.0832.1032 need to get the 32 (one after 08).
10.01.0832.1064 need to get the 32 (one after 08).
Please advice

Viewing 15 posts - 1 through 15 (of 19 total)

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

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

sharonsql2013 (3/4/2016)
I am trying to get a substring of characters but need to go from right
Example 9.0.0832.1032 need to get the 32 (one after 08).
10.01.0832.1064 need to get the 32 (one after 08).
Please advice

Will these strings always have the same format? Meaning four group of numbers divided by points?

What defines 08? What happens if there's no 08?

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

REVERSE the string, use SUBSTRING, and then REVERSE it again.

John

Lowell SSC Guru Points: 323504 More actions · Answer 3

There's a nifty builtin function called PARSENAME that splits a string like an Application Versions, IP addresses or Servername.Databasename.SchemaName.TableName into it's parts.

you can use that to isolate the second section from the right, and then substring the section like this:

With MyCTE(val)

AS

(

SELECT '9.0.0832.1032' UNION ALL --need to get the 32 (one after 08).

SELECT '10.01.0832.1064'

)

SELECT val,parsename(val,2) As Sectn,RIGHT(parsename(val,2),2) TargetString FROM MyCTE

/*

val Sectn TargetString

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

9.0.0832.1032 0832 32

10.01.0832.1064 0832 32

*/

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!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

John Mitchell-245523 (3/4/2016)
REVERSE the string, use SUBSTRING, and then REVERSE it again.
John

Got code?

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Jeff Moden (3/4/2016)
John Mitchell-245523 (3/4/2016)
REVERSE the string, use SUBSTRING, and then REVERSE it again.
John
Got code?

(Didn't see this response when it was posted.)

No, I haven't. I've suggested to the original poster how she might do it. I'm sure if she were struggling with any aspect of it she'd have posted back for more help. Lowell's solution looks better than mine, in any case.

John

Sean Lange SSC Guru Points: 286573 More actions · Answer 6

volodymyrlev228 (7/20/2016)
What about reverse the string, and then, reverse the string again? It finally helped me to

--URL REMOVED.

This sure looks a LOT like spam. Care to explain why that link is in your answer??

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

John Mitchell-245523 (7/20/2016)
Jeff Moden (3/4/2016)
John Mitchell-245523 (3/4/2016)
REVERSE the string, use SUBSTRING, and then REVERSE it again.
John
Got code?
(Didn't see this response when it was posted.)
No, I haven't. I've suggested to the original poster how she might do it. I'm sure if she were struggling with any aspect of it she'd have posted back for more help. Lowell's solution looks better than mine, in any case.
John

Understood. The reason why I was asking is because REVERSE is kind of expensive as functions go. There's normally a way to do what is asked with a single reverse and wanted to see what you were doing so I could make an alternate suggestion. Thanks, John.

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

Smendle Hall of Fame Points: 3969 More actions · Answer 8

Jeff Moden (3/4/2016)
John Mitchell-245523 (3/4/2016)
REVERSE the string, use SUBSTRING, and then REVERSE it again.
John
Got code?

Something like this?

With MyCTE(val)

AS

(

SELECT '9.0.0832.1032' UNION ALL --need to get the 32 (one after 08).

SELECT '10.01.0832.1064'

)

SELECT SUBSTRING(REVERSE(SUBSTRING(REVERSE(val),1,len(val)-(CHARINDEX('.08',(val),1)))),3,2) FROM MyCTE

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Smendle (7/20/2016)
Jeff Moden (3/4/2016)
John Mitchell-245523 (3/4/2016)
REVERSE the string, use SUBSTRING, and then REVERSE it again.
John
Got code?
Something like this?
With MyCTE(val)
AS
(
SELECT '9.0.0832.1032' UNION ALL --need to get the 32 (one after 08).
SELECT '10.01.0832.1064'
)
SELECT SUBSTRING(REVERSE(SUBSTRING(REVERSE(val),1,len(val)-(CHARINDEX('.08',(val),1)))),3,2) FROM MyCTE

Kind of but I'm pretty sure the presence of the .08 isn't reliable.

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Jeff Moden (7/20/2016)
John Mitchell-245523 (7/20/2016)
Jeff Moden (3/4/2016)
John Mitchell-245523 (3/4/2016)
REVERSE the string, use SUBSTRING, and then REVERSE it again.
John
Got code?
(Didn't see this response when it was posted.)
No, I haven't. I've suggested to the original poster how she might do it. I'm sure if she were struggling with any aspect of it she'd have posted back for more help. Lowell's solution looks better than mine, in any case.
John
Understood. The reason why I was asking is because REVERSE is kind of expensive as functions go. There's normally a way to do what is asked with a single reverse and wanted to see what you were doing so I could make an alternate suggestion. Thanks, John.

Ah yes, you can use CHARINDEX and just one REVERSE. Much better.

John

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

John Mitchell-245523 (7/21/2016)
Ah yes, you can use CHARINDEX and just one REVERSE. Much better.

'Zactly! 🙂

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

Smendle Hall of Fame Points: 3969 More actions · Answer 12

Jeff Moden (7/20/2016)
Smendle (7/20/2016)
Jeff Moden (3/4/2016)
John Mitchell-245523 (3/4/2016)
REVERSE the string, use SUBSTRING, and then REVERSE it again.
John
Got code?
Something like this?
With MyCTE(val)
AS
(
SELECT '9.0.0832.1032' UNION ALL --need to get the 32 (one after 08).
SELECT '10.01.0832.1064'
)
SELECT SUBSTRING(REVERSE(SUBSTRING(REVERSE(val),1,len(val)-(CHARINDEX('.08',(val),1)))),3,2) FROM MyCTE
Kind of but I'm pretty sure the presence of the .08 isn't reliable.

I know it was a littoral solution but I think the point was made...

This should get you the same result but be after the second '.' if it will only ever be at min 4 characters from the left.

SELECT SUBSTRING(REVERSE(SUBSTRING(REVERSE(val),1,len(val)-(CHARINDEX('.',(val),4)))),3,2) FROM MyCTE

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 13

Smendle (8/3/2016)
Jeff Moden (7/20/2016)
Smendle (7/20/2016)
Jeff Moden (3/4/2016)
John Mitchell-245523 (3/4/2016)
REVERSE the string, use SUBSTRING, and then REVERSE it again.
John
Got code?
Something like this?
With MyCTE(val)
AS
(
SELECT '9.0.0832.1032' UNION ALL --need to get the 32 (one after 08).
SELECT '10.01.0832.1064'
)
SELECT SUBSTRING(REVERSE(SUBSTRING(REVERSE(val),1,len(val)-(CHARINDEX('.08',(val),1)))),3,2) FROM MyCTE
Kind of but I'm pretty sure the presence of the .08 isn't reliable.
I know it was a littoral solution but I think the point was made...
This should get you the same result but be after the second '.' if it will only ever be at min 4 characters from the left.
SELECT SUBSTRING(REVERSE(SUBSTRING(REVERSE(val),1,len(val)-(CHARINDEX('.',(val),4)))),3,2) FROM MyCTE

You don't need the overhead of two REVERSEs though. In fact, if the presence of 4 elements separate by 3 periods is consistent, you don't need REVERSE or substring at all. You can use PARSENAME to quickly to the job as Lowell did.

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

Smendle Hall of Fame Points: 3969 More actions · Answer 14

Jeff Moden (8/3/2016)
Smendle (8/3/2016)
Jeff Moden (7/20/2016)
Smendle (7/20/2016)
Jeff Moden (3/4/2016)
John Mitchell-245523 (3/4/2016)
REVERSE the string, use SUBSTRING, and then REVERSE it again.
John
Got code?
Something like this?
With MyCTE(val)
AS
(
SELECT '9.0.0832.1032' UNION ALL --need to get the 32 (one after 08).
SELECT '10.01.0832.1064'
)
SELECT SUBSTRING(REVERSE(SUBSTRING(REVERSE(val),1,len(val)-(CHARINDEX('.08',(val),1)))),3,2) FROM MyCTE
Kind of but I'm pretty sure the presence of the .08 isn't reliable.
I know it was a littoral solution but I think the point was made...
This should get you the same result but be after the second '.' if it will only ever be at min 4 characters from the left.
SELECT SUBSTRING(REVERSE(SUBSTRING(REVERSE(val),1,len(val)-(CHARINDEX('.',(val),4)))),3,2) FROM MyCTE
You don't need the overhead of two REVERSEs though. In fact, if the presence of 4 elements separate by 3 periods is consistent, you don't need REVERSE or substring at all. You can use PARSENAME to quickly to the job as Lowell did.

I was specifically answering this question from you.... It was NOT meant to be a solution per se...

John Mitchell-245523 (3/4/2016)
REVERSE the string, use SUBSTRING, and then REVERSE it again.
John
Got code?
--Jeff Moden