Post reply

SELECT LIKE statment

  • September 9, 2005 at 12:08 pm

    #87630

     

    I'm having issues using the LIKE command.  I have a column in a database that contains serial numbers from different vendors.  Some of the serial numbers have spaces, some don't.  For example:

    '444 54 32222 33'

    '3239483721dfvv'

    'gf4837 1 24 54 78'

    I have a form on a website that searches for these serial numbers. However, the LIKE statement doesn't return anything if the user enters in the claim without spaces.  For example, if the user is searching for:

    '444 54 32222 33'

    and they type:

    '444543222233'

    No results will get returned.  Is there a way around this ?

    Thanks!

  • September 9, 2005 at 12:10 pm

    #588836

    Create an indexed calculated column that strips out the spaces. Then do the search on the column (making sure that the search parameter doesn't have spaces too).

  • September 9, 2005 at 12:11 pm

    #588837

    Use the REPLACE function in both your input and the field searched to remove spaces for the match.    (Don't worry, someone will probably give you a better solution that may not ruin your index if one exists on this field... ). 

    I wasn't born stupid - I had to study.

  • September 9, 2005 at 12:12 pm

    #588838

    Wow - no sooner said, than done...  

    I wasn't born stupid - I had to study.

  • September 9, 2005 at 12:14 pm

    #588841

    Well sooner done than said... You were still a little late on this one .

  • September 9, 2005 at 12:14 pm

    #588840

    Forgive my ignornace, but how does one create an indexed calculated column that strips out the spaces?

    Thanks!

  • September 9, 2005 at 12:18 pm

    #588843

    ALTER TABLE dbo.Temps ADD

     demo  AS replace(EPNOM, ' ', '')

    GO

    CREATE NONCLUSTERED INDEX IX_Temps_Demo ON dbo.Temps (demo)

  • September 9, 2005 at 12:35 pm

    #588849

    Ok, I understand creating an index...but what is dbo.Temps?  Is that a table that I first need to create?  And I assume the demo table is the table I'm using to actually do the query on.

     

  • September 9, 2005 at 12:37 pm

    #588851

    dbo.Temps is the table name

    demo is the name of the new column.

  • September 9, 2005 at 12:40 pm

    #588854

    Oh I see....

    What does EPNOM mean?  I googled it and couldn't find much of anything.

     

     

  • September 9, 2005 at 12:41 pm

    #588855

    You would put the real name of your table and real name of the column you want to create in their place...

    I wasn't born stupid - I had to study.

  • September 9, 2005 at 12:47 pm

    #588857

    EPNOM is the name of the other column you need to replace the spaces from.

  • September 9, 2005 at 12:52 pm

    #588859

    Oh I see...

    I ran the query and got this error:

    Warning! The maximum key length is 900 bytes. The index 'IX_serialnumbers_serialsnospaces' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.

  • September 9, 2005 at 1:02 pm

    #588861

    Doh...

    try it like this :

    LEFT(REPLACE(Colname, ' ', ''), (length of the original column)

  • September 9, 2005 at 1:07 pm

    #588863

    If I'm using the replace function when creating this new column, how come I can't just use the replace function when doing the original query?  That way I wouldn't have to worry about a whole new column to update.

     

    Thanks!

     

Viewing 15 posts - 1 through 15 (of 48 total)

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