Need a help in writing a query

Question

Need a help in writing a query

sarwar.ali490

Old Hand

Points: 382
More actions
December 11, 2016 at 6:42 pm

#401194

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.

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

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

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

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?

😎

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

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

sarwar.ali490 Old Hand Points: 382 More actions · Answer 5

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

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

John