Need a help in writing a query

  • Hi All,

    I got a requirement to validate multiple conditions in a given string.Eg STRING is a 30 byte key.I have to check

    1.Length is >10 and

    2.susbtring(1,6) in(e.g..123456,67892) and

    3.if susbrting(1,6)=123456 then check position 7 is=1 and

    4.if susbrting(1,6)=67890 then check position 7=2

    I have to check all these conditions in single where clause.if all these satifies then i am inserting it into main table else to error table giving different error codes based on which condition is failing.

    Thanks.

  • sarwar.ali490 (12/11/2016)


    Hi All,

    I got a requirement to validate multiple conditions in a given string.Eg STRING is a 30 byte key.I have to check

    1.Length is >10 and

    2.susbtring(1,6) in(e.g..123456,67892) and

    3.if susbrting(1,6)=123456 then check position 7 is=1 and

    4.if susbrting(1,6)=67890 then check position 7=2

    I have to check all these conditions in single where clause.if all these satifies then i am inserting it into main table else to error table giving different error codes based on which condition is failing.

    Thanks.

    Can you please post some sample data and the expected results?

    ๐Ÿ˜Ž

  • WHERE LEN(MyCol) > 10

    AND (MyCol LIKE '_____[1-6]1%'

    OR MyCol LIKE '_____[7-9]2%'

    OR MyCol LIKE '_____[0]2%')

    The digit 6 appears in both your "1" and "2" requirements, so I guessed where to put it.

    John

  • John Mitchell-245523 (12/12/2016)


    WHERE LEN(MyCol) > 10

    AND (MyCol LIKE '_____[1-6]1%'

    OR MyCol LIKE '_____[7-9]2%'

    OR MyCol LIKE '_____[0]2%')

    The digit 6 appears in both your "1" and "2" requirements, so I guessed where to put it.

    John

    Which could be pushed into an APPLY block for extra flexibility:

    CROSS APPLY (

    SELECT ErrorCode = CASE

    WHEN LEN(MyCol) > 10 THEN 1

    WHEN (MyCol LIKE '_____[1-6]1%' THEN 2

    WHEN MyCol LIKE '_____[7-9]2%' THEN 3

    WHEN MyCol LIKE '_____[0]2%') THEN 4

    ELSE NULL END

    ) v

    WHERE v.ErrorCode IS NOT NULL

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • How can this be possible?

    4.if susbrting(1,6)=67890 then check position 7=2

    If you only have 5 digits on the first 6 characters, then you shouldn't have a seventh character.

    However, I understood your problem like this:

    WHERE LEN(MyCol) > 10

    AND (MyCol LIKE '1234561%'

    OR MyCol LIKE '67890 2%');

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • ok.Let me give correct example.

    Key is 000SAQD11114165846563 and 000IDP1111112555555

    While loading from flat file i have to check

    1.if the len(key)>10(this i can do)

    2. and if(susbtring(key,1,6)='000IDP') then check for the 13th position if it is '2'

    2.and if(susbtring(key,1,6)='00SAQD') then check for the 13th position if it is '1'

    Thanks all for the help

  • Same principle. Check for [font="Courier New"]LIKE '000IDP______2%'[/font] or [font="Courier New"]'00SAQD______1%'[/font].

    John

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

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