December 11, 2016 at 6:42 pm
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.
December 12, 2016 at 1:59 am
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?
๐
December 12, 2016 at 2:13 am
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
December 12, 2016 at 2:25 am
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
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
December 12, 2016 at 8:47 am
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%');
December 12, 2016 at 9:13 am
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
December 12, 2016 at 9:20 am
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