Numeric version of DIFFERENCE or SOUNDEX?
-
My situation is this.
I need to check a table to see if a partnumber (varchar) exists in it. That's easy! But what if the value has two characters transposed?
It appears to me that SOUNDEX and DIFFERENCE completely ignore numbers.
I.E.
select soundex('1644700809a'), soundex('5xreww332')
will return '0000', '0000'
-and-
select difference('1644700809', '1644708009')
will return 4 (when they are actually similar)
If anyone can point me in the right direction I would really appreciate it.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Talk about time...This might be a step in the right direction for you...just posted yesterday.
-
I think this is a better approach for this task. But you are right otherwise Andrew!
DECLARE @Search VARCHAR(10),
@MaxDigitsWrong TINYINT
SELECT @Search = '1644705509',
@MaxDigitsWrong = 2
DECLARE @Test TABLE (PartNo VARCHAR(10))
INSERT @Test
SELECT '1644700809' UNION ALL
SELECT '1644700808' UNION ALL
SELECT '1644708509'
SELECT x.PartNo
FROM (
SELECT t.PartNo,
n.Number,
SUBSTRING(PartNo, n.Number, 1) c
FROM @Test t
CROSS JOIN (
SELECT DISTINCT Number
FROM master..spt_values
WHERE Number BETWEEN 1 AND 10
) n
) x
INNER JOIN (
SELECT DISTINCT Number,
SUBSTRING(@Search, Number, 1) c
FROM master..spt_values
WHERE Number BETWEEN 1 AND 10
) s ON s.Number = x.Number
GROUP BY x.PartNo
HAVING SUM(CASE WHEN x.c = s.c THEN 0 ELSE 1 END) <= @MaxDigitsWrong
N 56°04'39.16"
E 12°55'05.25" -
You can also use the Levesthein Edit Distance algorithm found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51540
N 56°04'39.16"
E 12°55'05.25" -
"...WHERE Number BETWEEN 1 AND 10"
Why not?
"...WHERE Number BETWEEN 0 AND 9"
-
Peter, THANK YOU for the help. Your first solution works partially, but it would incorrectly not match '12345' to 'a12345'. And I altered the spt_values to handle varying lengths of part numbers.
I REALLY appreciate the help. The link helped as well and I think I'm on the right track now!
If you're curious, here's my adapted solution:
declare @partnoLength int
set @partnoLength = len(@partno)
SELECT
DISTINCT
x.reportablepartid,
x.partdesc,
x.partnumber
FROM
(
SELECT t.reportablepartid, t.partnumber, t.partdesc, n.Number, SUBSTRING(partnumber, n.Number, 1) c
FROM reportableparttable t
CROSS JOIN
(
SELECT DISTINCT Number
FROM master..spt_values
WHERE Number BETWEEN 1 AND @partnoLength
) n
WHERE t.jobid = @jobid
) x
INNER JOIN
(
SELECT DISTINCT Number, SUBSTRING(@partno, Number, 1) c
FROM master..spt_values
WHERE Number BETWEEN 1 AND @partnoLength
) s ON s.Number = x.Number
GROUP BY x.reportablepartid, x.partnumber, x.partdesc
HAVING
SUM(CASE WHEN x.c = s.c THEN 0 ELSE 1 END) <= @maxPartDifference
UNION
SELECT
x.reportablepartid,
x.partdesc,
x.partnumber
FROM
(
SELECT t.reportablepartid, t.partnumber, t.partdesc, n.Number, SUBSTRING(reverse(partnumber), n.Number, 1) c
FROM reportableparttable t
CROSS JOIN
(
SELECT DISTINCT Number
FROM master..spt_values
WHERE Number BETWEEN 1 AND @partnoLength
) n
WHERE t.jobid = @jobid
) x
INNER JOIN
(
SELECT DISTINCT Number, SUBSTRING(reverse(@partno), Number, 1) c
FROM master..spt_values
WHERE Number BETWEEN 1 AND @partnoLength
) s ON s.Number = x.Number
GROUP BY x.reportablepartid, x.partnumber, x.partdesc
HAVING
SUM(CASE WHEN x.c = s.c THEN 0 ELSE 1 END) <= @maxPartDifference
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply