Post reply

Alphanumberic string search

  • February 2, 2010 at 7:55 am

    #215217

    Hi All,

    Here is a query,

    SELECT LASTNAME FROM CPM.dbo.T_CANADIAN

    WHERE '678567Z' BETWEEN '673001P' AND '679000P'

    It should not display any result as the where value numerials fall in between '673001P' AND '679000P', but the value is '678567Z' , since the last character is 'Z', it should not diaplay any result, but it still displays values when i execute the query.

    Someone help.

  • February 2, 2010 at 8:05 am

    #1112292

    In which case you need to check the right-most character of the value first, and then if that is within range you can check the numeric part.

    BrainDonor.

    Steve Hall
    Linkedin
    Blog Site

  • February 2, 2010 at 8:07 am

    #1112293

    Please tell me how to do it.

  • February 2, 2010 at 8:15 am

    #1112303

    sql_ques (2/2/2010)

    Please tell me how to do it.

    Look in BOL at the LEFT, RIGHT and SUBSTRING functions.

    It looks like the root issue here is a single data field with multiple pieces of data... why else would it matter that you have to search for stuff ending in a range where it ENDS with the same. It sounds like this field needs to be broken down into two fields.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 2, 2010 at 8:18 am

    #1112304

    sql_ques (2/2/2010)

    Please tell me how to do it.

    No.

    I will however take as long as is required to help you to work out how to write it. That is how you learn the more fundamental aspects of SQL Server. I can be very patient, as long as you don't give in, as you did with:

    http://qa.sqlservercentral.com/Forums/Topic837177-8-1.aspx

    and

    http://qa.sqlservercentral.com/Forums/Topic835685-1291-1.aspx

    There are three commands of interest - LEFT, RIGHT and SUBSTRING (probably using LEN, if these values have different lengths).

    Have a look at them and give it some thought. Come back with an attempt at the solution and we'll be happy to help.

    Steve Hall
    Linkedin
    Blog Site

  • February 2, 2010 at 8:55 am

    #1112332

    SELECT LASTNAME FROM CPM.dbo.T_CANADIAN

    WHERE '678567' BETWEEN '673001' AND '679000'

    AND 'Z' BETWEEN 'P' AND 'P'

    this worked.

  • February 2, 2010 at 9:03 am

    #1112342

    Would this work in all cases? Why dont you try to use the functions that we re hinted to you by others?

    -Roy

  • February 2, 2010 at 9:03 am

    #1112343

    sql_ques (2/2/2010)

    SELECT LASTNAME FROM CPM.dbo.T_CANADIAN

    WHERE '678567' BETWEEN '673001' AND '679000'

    AND 'Z' BETWEEN 'P' AND 'P'

    this worked.

    Indeed it will. However, you've had to split the values yourself - originally it was

    WHERE '678567Z' BETWEEN '673001P' AND '679000P'

    Assuming '678567Z' and the others are actually column values within the database, how would you get SS to spilt them within the code? You need to replicate the result that you've achieved, but by splitting the string via SQL statements.

    brainDonor.

    Steve Hall
    Linkedin
    Blog Site

  • February 2, 2010 at 9:09 am

    #1112347

    Roy Ernest (2/2/2010)

    Would this work in all cases? Why dont you try to use the functions that we re hinted to you by others?

    Those were hints? Heck, IMHO, those were just 1 short step shy of the answer itself.:w00t:

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 2, 2010 at 9:14 am

    #1112350

    sql_ques (2/2/2010)

    Hi All,

    Here is a query,

    SELECT LASTNAME FROM CPM.dbo.T_CANADIAN

    WHERE '678567Z' BETWEEN '673001P' AND '679000P'

    It should not display any result as the where value numerials fall in between '673001P' AND '679000P', but the value is '678567Z' , since the last character is 'Z', it should not diaplay any result, but it still displays values when i execute the query.

    Someone help.

    Based on the OP history of deleting posts, I just wanted to keep it around for prosteriety (sp?).

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 2, 2010 at 9:59 am

    #1112382

    sql_ques (2/2/2010)

    SELECT LASTNAME FROM CPM.dbo.T_CANADIAN

    WHERE '678567' BETWEEN '673001' AND '679000'

    AND 'Z' BETWEEN 'P' AND 'P'

    this worked.

    I actually doubt that this query would return anything but an empty result set.

    Reason:

    The WHERE part "AND 'Z' BETWEEN 'P' AND 'P'" will always be false since 'Z' will never be between 'P' and 'P'.

    Therefore, the whole WHERE condition will result on zero matching rows.

    Just wondering...


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 2, 2010 at 10:10 am

    #1112393

    lmu92 (2/2/2010)

    sql_ques (2/2/2010)

    SELECT LASTNAME FROM CPM.dbo.T_CANADIAN

    WHERE '678567' BETWEEN '673001' AND '679000'

    AND 'Z' BETWEEN 'P' AND 'P'

    this worked.

    I actually doubt that this query would return anything but an empty result set.

    Reason:

    The WHERE part "AND 'Z' BETWEEN 'P' AND 'P'" will always be false since 'Z' will never be between 'P' and 'P'.

    Therefore, the whole WHERE condition will result on zero matching rows.

    Just wondering...

    That is my inclination as well. I don't see how this evaluates to anything and then returns a valid result-set.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 2, 2010 at 11:54 am

    #1112454

    And yet, if you look at the last line of the OPs original post, this is exactly what he wants.... for now.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 2, 2010 at 12:09 pm

    #1112464

    WayneS (2/2/2010)

    And yet, if you look at the last line of the OPs original post, this is exactly what he wants.... for now.

    So, do you think having a persisted calculated column that moves the last character to the first position of the value would be one of the possible answers that even might help performance?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 2, 2010 at 12:14 pm

    #1112474

    lmu92 (2/2/2010)

    sql_ques (2/2/2010)

    SELECT LASTNAME FROM CPM.dbo.T_CANADIAN

    WHERE '678567' BETWEEN '673001' AND '679000'

    AND 'Z' BETWEEN 'P' AND 'P'

    this worked.

    I actually doubt that this query would return anything but an empty result set.

    Reason:

    The WHERE part "AND 'Z' BETWEEN 'P' AND 'P'" will always be false since 'Z' will never be between 'P' and 'P'.

    Therefore, the whole WHERE condition will result on zero matching rows.

    Just wondering...

    Why not just do

    SELECT LASTNAME FROM CPM.dbo.T_CANADIAN

    WHERE 1 <> 2

    That should provide an empty result set too.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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