Odd String Replacement of Characters

Question

Odd String Replacement of Characters

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
October 24, 2010 at 1:25 pm

#228236

Hello Everyone
I am working on some things, and ran across this to try and figure out. This is very close to my other post that Wildcat answered.
I have a string that looks like this:
10494B-XL-Large.jpg
What I am working on is to write some code that will return only this:
10494B-XL
Each string has two hyphens, but not always in the same character location.
Does anyone have anything that will work that I can try?
Thanks
Andrew SQLDBA

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You want to remove the second hyphen and everything after it?

If there will only ever be two hyphens, reverse the string, take everything to the right of the first hyphen, then reverse the result.

REVERSE, CHARINDEX, LEN and RIGHT are the functions that you'll be needing.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

Gail

What a great idea. Let me work on that to see if I can get it.

Thank you

Andrew SQLDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Old, old trick. Most commonly used for extracting just the file name or just the folders from a full path - C:\Some\Odd\Dir\Thisfile.txt

Not just a T-SQL trick.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

I am coming close, but there is something that I am missing. I will keep at it to see if I can figure it out.

Thanks for your help in pointing me in the correct direction

AndrewSQLDBA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I lied, no need for LEN. :hehe:

DECLARE @FileName VARCHAR(50) = '10494B-XL-Large.jpg';

SELECT REVERSE(RIGHT(REVERSE(@FileName), CHARINDEX('-', REVERSE(@FileName) ,0)-1));

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 6

If there's always two dashes, REVERSE (especially two reverses) is a bit expensive. This will work if there's always two dashes and be a bit faster.

DECLARE @FileName VARCHAR(50)

SELECT @FileName = '10494B-XL-Large.jpg';

SELECT SUBSTRING(@FileName,1,CHARINDEX('-',@FileName,CHARINDEX('-',@FileName)+1)-1)

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