Urgent help with STRING in SQL

• dhananjay.nagarkar

  Default port

  Points: 1496

  More actions

  April 5, 2014 at 11:18 am

  #290013

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 5, 2014 at 11:48 am

  #1703511

  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]%';

• dhananjay.nagarkar

  Default port

  Points: 1496

  More actions

  April 5, 2014 at 8:42 pm

  #1703557

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 5, 2014 at 10:05 pm

  #1703560

  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;

• dhananjay.nagarkar

  Default port

  Points: 1496

  More actions

  April 7, 2014 at 2:03 pm

  #1703947

  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

• dhananjay.nagarkar

  Default port

  Points: 1496

  More actions

  April 7, 2014 at 2:45 pm

  #1703958

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 7, 2014 at 3:21 pm

  #1703966

  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.

• dhananjay.nagarkar

  Default port

  Points: 1496

  More actions

  April 7, 2014 at 3:31 pm

  #1703967

  Thanks Lynn.

  Regards

  DJ