String functions in a SQL where clause

Question

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.

you'll never get an index used doing string manipulations like that in the WHERE. If you run this query a lot and will have many rows, you should use look into a computed column and a persisted index. — KM.
– KM., Commented May 1, 2012 at 18:59

SQLMason · Accepted Answer · 2012-05-01 18:54:47Z

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

Marc B · Accepted Answer · 2012-05-01 18:48:30Z

0

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.

answered May 1, 2012 at 18:48

Marc B

362k44 gold badges433 silver badges508 bronze badges

9 Comments

masfenix Over a year ago

Suppose I didnt know the length of the string. Then what

Aaron Bertrand Over a year ago

@masfenix Then please supply more realistic sample data and expected results.

Marc B Over a year ago

Then you'd have to dynamically determine which positions need to be compared, e.g. if you're comparing the string backwards (last 6 chars, then last 10-8 chars, etc...) you're in for a BUTT UGLY query, and definitely would be helped by splitting the barcode up into individual fields.

SQLMason Over a year ago

That's not the opposite of: "digits 7 and 8 are the same thing in barcode1 plus 20"

masfenix Over a year ago

I know, i was just fishing for a query. Marc's original answer was okay initially. Also I dont know if I should create a specfic question for this but lets say I just want to replace the 2nd character in a string with a dash. How would I do that?

|

Collectives™ on Stack Overflow

String functions in a SQL where clause

2 Answers 2

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related