April 5, 2014 at 11:18 am
Hi friends,
I need a urgent help with SQL String validation please-
I have a field in DB that is nvarchar(500) named as “GHRMS Contingent ID” field, as per requirement i have to do the following hecks for this field to get the corerct value-
If the first 8 characters of this string field meet one of the following two scenarios:
1. All 8 are numeric-no special characters or spaces
2. 1st character is alpha and next 7 are numeric-no special character or spaces
Then use that value of that field.
Thanks
DJ
April 5, 2014 at 11:48 am
Something like this?
declare @TestTab table (
GHRMSContingentID nvarchar(500)
);
insert into @TestTab
values (N'01234567oewqronasdf'),(N'A1234567uhcvoibndaweo'),(N'oadooeoawe');
select * from @TestTab;
select * from @TestTab where GHRMSContingentID like N'[A-Za-z0-9][0-9][0-9][0-9][0-9][0-9][0-9]%';
April 5, 2014 at 8:42 pm
Hi Lynn,
i tried it this way-\--
when [w].[GHRMS Contingent ID] IS NOT NULL
-- if the 1st 8 characters meet All 8 are numeric-no special characters or spaces
and ISNUMERIC(SUBSTRING(w.[GHRMS Contingent ID],0,9)) = 1 -- 1st eight are numeric
-- 1st character is alpha and next 7 are numeric-no special character or spaces
or(
SUBSTRING(w.[GHRMS Contingent ID], 1,1) like '%[a-z0-9]%'
and ISNUMERIC(SUBSTRING(w.[GHRMS Contingent ID], 2, 7)) = 1)
then LEFT(w.[GHRMS Contingent ID],8)
---
seems to work but please free if any issue you may see with it.
Thanks
DJ
April 5, 2014 at 10:05 pm
dhananjay.nagarkar (4/5/2014)
Hi Lynn,i tried it this way-\--
when [w].[GHRMS Contingent ID] IS NOT NULL
-- if the 1st 8 characters meet All 8 are numeric-no special characters or spaces
and ISNUMERIC(SUBSTRING(w.[GHRMS Contingent ID],0,9)) = 1 -- 1st eight are numeric
-- 1st character is alpha and next 7 are numeric-no special character or spaces
or(
SUBSTRING(w.[GHRMS Contingent ID], 1,1) like '%[a-z0-9]%'
and ISNUMERIC(SUBSTRING(w.[GHRMS Contingent ID], 2, 7)) = 1)
then LEFT(w.[GHRMS Contingent ID],8)
---
seems to work but please free if any issue you may see with it.
Thanks
DJ
I see a lot extra work being done when all you need is what is in the last select query below:
declare @TestTab table (
GHRMSContingentID nvarchar(500)
);
insert into @TestTab
values (N'01234567oewqronasdf'),(N'A1234567uhcvoibndaweo'),(N'Aa1234567uhcvoibndaweo'),(N'oadooeoawe'),(null);
select * from @TestTab;
select
case when w.GHRMSContingentID like N'[A-Za-z0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
then left(w.GHRMSContingentID,8)
else N'Invalid'
end as IDValue
from @TestTab w;
April 7, 2014 at 2:03 pm
Thanks much Lynn that really cool ti works .
but just for my understanding sake wanted to learn what does this line do - GHRMSContingentID like N'[A-Za-z0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
? in simple ay man terms please.
Regards
DJ
April 7, 2014 at 2:45 pm
thanks so much Lyn for the one liner code for my requirement-
If the first 8 characters meet one of the following two scenarios:
1. All 8 are numeric-no special characters or spaces
2. 1st character is alpha and next 7 are numeric-no special character or spaces--> it does indeed satisfy both scenarios check for me. awesome.
Regards
DJ
April 7, 2014 at 3:21 pm
dhananjay.nagarkar (4/7/2014)
Thanks much Lynn that really cool ti works .but just for my understanding sake wanted to learn what does this line do - GHRMSContingentID like N'[A-Za-z0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
? in simple ay man terms please.
Regards
DJ
Simple pattern matching. The first position contains a any alpha character from A to Z (either capital or lower case) or the numeric character 0 through 9, followed by seven more numeric characters 0 through 9 in each of the positions.
Look it up in BOL (Books Online), it explains it fairly well.
April 7, 2014 at 3:31 pm
Thanks Lynn.
Regards
DJ
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply