Help with SQL Select with ^^^^

  • srisadhu

    SSC Enthusiast

    Points: 117

    I am trying to write select statement for a column value that looks like this: "PQ5|1|896^^^^57" where 896 and 57 change. They will be numbers only. Also, initial few chars wont change. There could be 3 or 4 Caret symbols. I need only results that have 4 carets and not 3 carets.

    And where clause looks like this:

    where col like '%PQ5|1|[0-9][^][^][^][^]%'  - Picks up rows with 3 carets.

    Where col like '%PQ5|1|[0-9]\^\^\^\^%'  escape '\'  - No Results.

    Can someone help me this. Cant Understand what SQL Server is doing.

     

     

     

     

     

  • Jeffrey Williams

    SSC Guru

    Points: 88549

    Assuming you want to parse out the data - you could use CHARINDEX to find the start positions of each segment and then use SUBSTRING to get the individual values.  If you only need to check the values then you just need to form the LIKE statement correctly:

    Declare @testTable Table (TestValue varchar(20));

    Insert Into @testTable (TestValue)
    Values ('PQ5|1|896^^^^57')
    , ('PQ5|1|911^^^^53')
    , ('PQ5|1|201^^^27')
    , ('PQ6|1|896^^^^57');

    Select *
    From @testTable tt
    Cross Apply (Values (charindex('|', tt.TestValue, 1))) As p1(pos)
    Cross Apply (Values (charindex('|', tt.TestValue, p1.pos + 1))) As p2(pos)
    Cross Apply (Values (substring(tt.TestValue, 1, p2.pos - 1))) As s1(SubValue)
    Cross Apply (Values (substring(tt.TestValue, p2.pos + 1, len(tt.TestValue) - p2.pos - 1))) As s2(SubValue)
    Where s1.SubValue = 'PQ5|1'
    And s2.SubValue Like '%^^^^%';

    Select *
    From @testTable tt
    Where tt.TestValue Like '%PQ5|1|%^^^^%';

    For future reference - it is much easier to get an answer if you provide a sample table with sample data and expected results.  I have shown one way to provide that data, you can find more at the link in my signature.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • srisadhu

    SSC Enthusiast

    Points: 117

    Hi, column value I provided has more content to left and right of the string I have provided. So, can't use charindex. Need to use regex. regex doesnot identify ^^^^. That's where i am struck.

  • Jeffrey Williams

    SSC Guru

    Points: 88549

    Please provide sample data that shows the full data and the expected results.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • sterling3721

    SSC-Addicted

    Points: 445

    if the pattern is "PQ5|1|896^^^^57", where "896" is  three-digit(ddd), "57" is two-digit(dd), this also works:

    where col like '%PQ[0-9]|[0-9]|[0-9][0-9][0-9]^^^^[0-9][0-9]%'

  • srisadhu

    SSC Enthusiast

    Points: 117

    But isn't ^ wild character for like and regular expression.

     

  • Jeffrey Williams

    SSC Guru

    Points: 88549

    srisadhu wrote:

    But isn't ^ wild character for like and regular expression.

    No, it is not a wildcard and like does not use regular expressions.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • srisadhu

    SSC Enthusiast

    Points: 117

    I just tested that and I think problem is with the [0-9].

    • Row1: akahdkahsd PQ5|1|896^^^^^^^tester
    • Row2: akahdkahsd PQ5|1|456^^^^^^^tester
    • Row3: akahdkahsd PQ5|1|895^^^^tester
    • Row4: akahdkahsd PQ5|1|8965^^^^^^^tester
    • Row5: akahdkahsd PQ5|1|54^^^RTH67^^^^tester

    Like clause is "%PQ5|1|896^^^^%" - Picks up row1.

    Like Clause is "%PQ5|1|[0-9]^^^^%" - Picks up nothing. Should have picked up rows 1,2, 3, 4

    Can someone advice pls.

  • drew.allen

    SSC Guru

    Points: 76737

    The code [0-9] searches for EXACTLY ONE DIGIT.  Your examples have 2-4 digits in that position, which is not exactly one, so they don't match.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • srisadhu

    SSC Enthusiast

    Points: 117

    Oh, if I only know that its a number and dont know the size, how do I do it?

    Very confusing for me. Cant find proper documentation.

     

  • Jeffrey Williams

    SSC Guru

    Points: 88549

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15

    https://docs.microsoft.com/en-us/sql/t-sql/functions/patindex-transact-sql?view=sql-server-ver15

    Did you even try my solutions?  The second one definitely works as you expect...

    Select *
    From @testTable tt
    Where tt.TestValue Like '%PQ5|1|%^^^^%';

    And if you could provide some real world examples - it would be much easier to provide a working example.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • drew.allen

    SSC Guru

    Points: 76737

    Jeffrey Williams wrote:

    srisadhu wrote:

    But isn't ^ wild character for like and regular expression.

    No, it is not a wildcard and like does not use regular expressions.

    LIKE absolutely does use regular expressions.  It's just not a very powerful regex language.  While things like quantification (?, *, {m,n}) are typical in regex languages, they are not necessary to be considered a regex language.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Jeff Moden

    SSC Guru

    Points: 996661

    Post deleted because the op seemed offended by advice on how to post such problems.,

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • srisadhu

    SSC Enthusiast

    Points: 117

    Just posting some solution that you can think of doensot make you pro and doesnot make ignorant.

    When someone posts on forum it means that they can't get through documentation(which you have agreed yourself).

    I have posted enough data for finding a pattern and I can figure out something myself. Thanks.

  • Jeff Moden

    SSC Guru

    Points: 996661

    srisadhu wrote:

    Just posting some solution that you can think of doensot make you pro and doesnot make ignorant.

    When someone posts on forum it means that they can't get through documentation(which you have agreed yourself).

    I have posted enough data for finding a pattern and I can figure out something myself. Thanks.

    I'm making suggestions to you on how you can get better answers more quickly. But, no problem... I've deleted my post so you can figure out something yourself.

     

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

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

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