Post reply

Query a Field and Ignore Commas and Periods in DB Value

  • June 14, 2018 at 9:10 am

    #406695

    Hi,

    I'm not sure how to query a field that contains commas and periods with like.

    Value in db: "Acme Products, Inc."

    User is querying with "Acme Products Inc"

    Of course, using a like statement will not return any values. How can I alter the query to ignore any punctuation marks?

    Thanks!!

  • June 14, 2018 at 9:24 am

    #1994015

    you could try 
    where replace (column_name,',','')='Acme Products Inc'

    or look to have the DB changed to include a csoundex column or even

    where soundex (column_name) =soundex('Acme Products Inc')

    ***The first step is always the hardest *******

  • June 14, 2018 at 9:25 am

    #1994016

    mcromarty - Thursday, June 14, 2018 9:10 AM

    Hi,

    I'm not sure how to query a field that contains commas and periods with like.

    Value in db: "Acme Products, Inc."

    User is querying with "Acme Products Inc"

    Of course, using a like statement will not return any values. How can I alter the query to ignore any punctuation marks?

    Thanks!!


    See the below illustration:
    create table punch
    (
    word varchar(200)
    );

    insert into punch values('Acme Products, Inc.');
    select * from
    (select replace(replace(word,',',''),'.','') as word from punch
    )rep
    where word='Acme Products Inc'

    Saravanan

  • June 14, 2018 at 9:34 am

    #1994018

    Thanks for the quick replies. Perfect!

  • June 14, 2018 at 10:30 am

    #1994028

    Just know that this will most likely result in a table or clustered index scan.  Depending on the query and indexes you may get a non clustered index scan.

  • June 14, 2018 at 10:45 am

    #1994034

    mcromarty - Thursday, June 14, 2018 9:34 AM

    Thanks for the quick replies. Perfect!

    What Lynn Pettis posted means that performance could be a problem because every time you run the querys posted, your SQL server will have to look at every single row, and the indexes won't help much.   If you have this kind of query running from a web page, you might want to consider creating a computed, persisted column that contains the data without any punctuation, and then index the table on that column, including any other columns that would get selected from that table.

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

  • June 14, 2018 at 5:03 pm

    #1994068

    sgmunson - Thursday, June 14, 2018 10:45 AM

    mcromarty - Thursday, June 14, 2018 9:34 AM

    Thanks for the quick replies. Perfect!

    What Lynn Pettis posted means that performance could be a problem because every time you run the querys posted, your SQL server will have to look at every single row, and the indexes won't help much.   If you have this kind of query running from a web page, you might want to consider creating a computed, persisted column that contains the data without any punctuation, and then index the table on that column, including any other columns that would get selected from that table.

    Which will work as long as the index doesn't exceed 900 bytes clustered or 1700 bytes non clustered (this is posted in a SQL 2017 forum).

  • June 14, 2018 at 9:30 pm

    #1994072

    We solved this problem by creating a persisted computed column that contains no punctuation and no spaces.  When the user types something to search for, we also strip that of punctuation and spaces and then do the compare.  Makes for very nice SARGable predicates.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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