Join on a substring value

Question

Join on a substring value

jeffshelix

SSCrazy

Points: 2257
More actions
June 12, 2018 at 10:16 am

#413860

One table has a Supplier Code, which is always 5 Alpha-Numeric.
I need to join on a table where that Supplier code is embedded in a string.
the string is always formatted as such:
XXXXXXXX-Supplier-ZZZZ (the prefix is variable length)
the number of characters in the prefix is not consistent, so i can't do a substring.
In Excel, i could do a FIND, then add 1, and pull the next 5 characters.
The suffix needn't come into play.
I can't get his to work in SQL (TBH, i don't know how) lol
thanks

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

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

ScottPletcher SSC Guru Points: 101099 More actions · Answer 1

WHERE/ON SUBSTRING(string, CHARINDEX('-', string) + 1, 5) = ...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jeffshelix SSCrazy Points: 2257 More actions · Answer 2

Thanks Scott!

I will review the function CHARINDEX!

Solved

DesNorton SSC-Insane Points: 24387 More actions · Answer 3

ALTER TABLE [YourSchema].[YourTable] ADD Supplier AS CAST(SUBSTRING([YourStringColumn], CHARINDEX('-', [YourStringColumn]) + 1, 5) AS CHAR(5)) PERSISTED;

Now you can index the [Supplier] field, and join on it directly.

How to post data/code on a forum to get the best help.

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

If this is needed on a regular basis, I recommend a persisted computed column that can be indexed.

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

ScottPletcher SSC Guru Points: 101099 More actions · Answer 5

Or, better yet, just go back to properly using 1NF in the table and store all values as atomic, i.e., individual columns. The computed column should be the concatenated value, not the other way around.

--XXXXXXXX-Supplier-ZZZZ
...
Xcolumn char(8) NOT NULL,
Supplier char(5) NOT NULL,
Zcolumn char(4) NOT NULL,
Concat_Column AS CAST(Xcolumn + '-' + Supplier + '-' + Zcolumn AS char(19),
...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

ScottPletcher - Tuesday, June 12, 2018 1:06 PM
Or, better yet, just go back to properly using 1NF in the table and store all values as atomic, i.e., individual columns. The computed column should be the concatenated value, not the other way around.

Amen to that!

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