CODEWITHSUNDEEP
sql-server
masfenix
masfenix

Reputation: 7986

String functions in a SQL where clause

Lets say I have a field. Lets call it Barcode1. Right now all Barcodes1 are 22 characters with each character is an integer. Suppose there is a second field Barcode2. Both of these are varchar(22)

My condition in plain english terms is: Barcode1 is identical to barcode2 except in digits 7,8 where for barcode2, digits 7 and 8 are the same thing in barcode1 plus 20

so 

001214**54**54545654521523
549462**74**48634842135782

I also would like the negation of the where clause where rows that do NOT match the condition are returned.

Thank you.

Upvotes: 0

Views: 3041

Answers (2)

SQLMason
SQLMason

Reputation: 3275

I think this is what you want:

Example Data: 

DECLARE @table TABLE ( barcode VARCHAR(22) )
INSERT  INTO @table
        (
          barcode
        )
        SELECT  '0012145454545654521523'
        UNION ALL
        SELECT  '0012142454545654521523'
        UNION ALL
        SELECT  '5494627448634842135782'
        UNION ALL
        SELECT  '5494625448634842135782'

First Condition - meets 7,8 + 20

SELECT  a.barcode,
        b.barcode,
        SUBSTRING(a.barcode, 7, 2) a,
        SUBSTRING(b.barcode, 7, 2) b
FROM    @table a
        INNER JOIN @table b
            ON SUBSTRING(a.barcode, 7, 2) + 20 = SUBSTRING(b.barcode, 7, 2)
               AND a.barcode != b.barcode

returns:

barcode                 barcode                  a  b
0012145454545654521523  5494627448634842135782  54  74
5494625448634842135782  5494627448634842135782  54  74

Negation where 7,8 + 20 doesn't exist

SELECT  *
FROM    @table a
WHERE   NOT EXISTS ( SELECT TOP 1 1
                     FROM   @table b
                     WHERE  SUBSTRING(a.barcode, 7, 2) + 20 = SUBSTRING(b.barcode, 7, 2) )

returns: 

0012142454545654521523
5494627448634842135782

Upvotes: 1

Marc B
Marc B

Reputation: 360602

You'll have to break that barcode up using string operations, something like:

WHERE
    substring(barcode1, 0, 6) = substring(barcode2, 0, 6) AND
    substring(barcode1, 9, 2) = substring(barcode2, 0, 9) AND
    etc...

And since you'll be doing these comparisons on function results, indexes aren't going to be used. If this is a frequent operation, you'd be better off splitting up the barcode strings into individual fields so you can compare the individual chunks as fully separate indexable fields.

Upvotes: 0

Related Questions