comparing two different string patterns

Question

Post reply

comparing two different string patterns

rgretz

SSC Veteran

Points: 264
More actions
April 20, 2010 at 8:01 am

#146590

I have inventory parts in a system that are the same family of parts and I want to compare them for reporting purposes. The part numbers have a string pattern that is the same for both but only certian characters in the string pattern are different. For example:
part sub family 'A' has a string pattern of '%XVD%'
part sub family 'B' has a string pattern of '%SVD%'
All the characters before and after the XVD and SVD are not relevent. If there is a part that has XVD in it then its sister part will be present with an SVD in it.
basically 01102XVD42010 is the same as 51102SVD37010. Is there a function that anyone has developed to do comparisons like or converting one pattern to another?

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

You might want to check out the REPLACE function in BOL.

You might also check out the LIKE function... you might be able to use it as LIKE '%[SX]VD%'.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lowell SSC Guru Points: 323518 More actions · Answer 2

rik there must be more to joining the families of parts;

if it was just that SVD matches XVD, then if i have ten rows in PartSubFamilyA, and only one row inPartSubFamilyB, that one "SVD" would match all ten rows of "XVD"; so one sister part would match everything, that's not right i'm sure; maybe there is a Part family, and also a part number?

is there another column you didn't mention?

if you can give us the table structures of the PartSub's, and a couple of rows of sample data, I'm sure we could give you a working SQL to prototype;

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!

rgretz SSC Veteran Points: 264 More actions · Answer 3

The part numbers are not mine. I need to make sure that if a part from subfamily a was shipped that the exact corresponding part from subfamily b was also shipped. for example:

1234xvd567 = 1234svd567

but 1234xvd567 <> 1234svd560

I do not know the number of characters before and after the xvd but they must match the same characters before and after in the sister part SVD.

I can use a replace but replace does not take wild card characters. I know the character pattern ( %XVD%) and I want to compare it to the sister part with some kind of function using the sister character pattern and have the function return a true/false

funca( stringpatternA, stringPatternB, stringa, stringB) returns true

or something equivelent

WayneS SSC Guru Points: 95460 More actions · Answer 4

You don't need wildcards to use replace... it replaces all occurrances.

i.e. select REPLACE('Hello World', 'or', '12') returns 'Hello W12ld'.

So, replace(columnname, 'XVD', 'SVD') would replace all occurrances of XVD w/ SVD.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

rik gretzinger

The following is just to give you an idea of how the

problem could be solved, it is NOT the fastest way,

and if your parts and subparts tables are large it will

require an inordinate amount of time to run the T-SQL

--To receive tested assistance please post your table definition

--Sample data, the result you need and what work you have already attempted

--Refer to the first link in my signature block for details

--This is what we are asking for if all the data where in a single table

CREATE TABLE #Parts (Partnum VARCHAR(20),Stocklevel INT)

INSERT INTO #Parts

SELECT '01102XVD42010',2 UNION ALL

SELECT '51102SVD37010',10 UNION ALL

SELECT '1234xvd567',5 UNION ALL

SELECT '01102SVD42010',2 UNION ALL

SELECT '1234svd560',6 UNION ALL

SELECT '1234svd567',6

--Since you did not state if all data was in a single table

--I assumed (and you know what that makes out of me)

--you have two distinct tables

--Create first distinct table, the subparts

--Being too lazy to repeat the creation and populating

--two separate tables I took the short way out

--SubParts

SELECT Partnum

INTO #Parts2

FROM #Parts WHERE partnum LIKE '%SVD%'

--Create main parts table

SELECT Partnum

INTO #Parts3

FROM #Parts WHERE partnum LIKE '%XVD%'

--Finally the T_SQL that returns what I think you want

SELECT p2.Partnum aS 'Substitute Part' FROM #Parts2 p2

INNER JOIN #Parts3 p ON

REPLACE (p.partnum,'XVD','SVD')= p2.Partnum

/* Result:

'Substitute Part'

01102SVD42010

1234svd567 */

--Running this T-SQL

SELECT * FROM #Parts2 p2

INNER JOIN #Parts3 p ON

REPLACE (p.partnum,'XVD','SVD')= p2.Partnum

/* Result:

Partnum Partnum

01102SVD4201001102XVD42010

1234svd5671234xvd567

clean up

DROP TABLE #Parts

DROP TABLE #Parts2

DROP TABLE #Parts3 */

Further I would encourage you to look at CTE (Common Table Expression) in Books On Line and you can most likely develope a

faster running T-SQL

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]