interior data not equal to another column

Question

interior data not equal to another column

jeffshelix

SSCrazy

Points: 2257
More actions
July 31, 2018 at 9:13 am

#355184

I hope i get this explained correctly.
1. My Column CI_Num has the format of AAAAA-ABCDE-BBBBB
The Prefix is of variable length. The Base (ABCDE) is always 5 characters, and the Suffix is of variable lenght
2. My Column Supplier code as the format ABCDE
The middle (base) part of the CI_Num should equal the Supplier Code. Both will always be 5 characters.
Due to a systems change at our vendor, they do not match.
I need to write a report that shows me the records where the the inside 5 characters in the CI_Num do not equal the Supplier Code.
Seemed easy, but no luck here. Thanks

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

jeffshelix - Tuesday, July 31, 2018 9:13 AM
I hope i get this explained correctly.
1. My Column CI_Num has the format of AAAAA-ABCDE-BBBBB
The Prefix is of variable length. The Base (ABCDE) is always 5 characters, and the Suffix is of variable lenght
2. My Column Supplier code as the format ABCDE
The middle (base) part of the CI_Num should equal the Supplier Code. Both will always be 5 characters.
Due to a systems change at our vendor, they do not match.
I need to write a report that shows me the records where the the inside 5 characters in the CI_Num do not equal the Supplier Code.
Seemed easy, but no luck here. Thanks

Is there a list of valid Supplier Codes somewhere?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

drew.allen SSC Guru Points: 76985 More actions · Answer 2

You asked this same exact question last month.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

jeffshelix SSCrazy Points: 2257 More actions · Answer 3

Thanks Drew . . . i REALLY wish my company would hire a trained SQL person . . Lol.

Apologies.

sgmunson SSC Guru Points: 110640 More actions · Answer 4

jeffshelix - Tuesday, July 31, 2018 10:00 AM
Thanks Drew . . . i REALLY wish my company would hire a trained SQL person . . Lol.
Apologies.

Where is your company located?

But back to the problem at hand - The ONLY way you'll get this solved is to find out a way to get to the new data (possibly a Linked Server?), and have access to your table on the same server where you can have access to the new data. Then you can write a query that compares the "middle" segment to the new data's Supplier code value. To get the middle segment, you have a couple of choices. You could use a string splitting function such as DelimitedSplit8K, found here:

http://www.sqlservercentral.com/articles/72993/

And you could integrate this snippet into your query:
CROSS APPLY ( SELECT S.Item AS SupplierCode FROM dbo.DelimitedSplit8K(YourTableAliasGoesHere.CI_Num, '-') AS S WHERE S.ItemNumber = 2 ) AS SC

What this would do is to use the dash as a delimiter, and pick up the different strings in order of appearance, and just take the second one. Alternatively, you could use CHARINDEX and SUBSTRING to find the middle segment. That function would have to become a part of your system, but it's reliable and has been around for at least the last 8 years. It's also fantastic in terms of performance. The code for the function is located at the end of the article I linked to.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)