how to check and change custom string in sql server

Question

i have problem in my sql query code i have one column for my codes and structure of code like this

3digit-1to3digit-5to7digit-1to2digit
xxx-xxx-xxxxxx-xx

in code column user add code like

1-1486414-305-115 --mistake
116-500-325663-1  --ok
116-2-2244880-1   --ok
121-512-2623075-1 --ok
122-500-1944261-3 --ok
2-2651274-500-147 --mistake 
1-2551671-305-147 --mistake 
124-500-329130-1  --ok

how to check and fix the mistake codes.

thanks for read my problem

You don't explain how to fix the data. If `'2-2651274-500-147'` is erroneous, what should the correct value be? — Thom A, Apr 23 '19 at 10:47
Use SPLIT and LEN functions. If Length is les than 3 then it is mistake — apomene, Apr 23 '19 at 10:48
yes user enter this code for description like jdajshd 1-1486414-305-115 this is ok code and udab dasd,and i get code entered in new col — dasdsadasdsad asdasdas, Apr 23 '19 at 10:49
You "correct" your GUI to inform and guide the user to the appropriate fix. After entry you cannot possibly know what the user intended to enter. — SMor, Apr 23 '19 at 11:32
Good question, I found a solution (see below) which extracts all the strings between the separator (-) using RECUSRSIVITY, and then compare the lenght of each string with the coditions you have, look at my solution below and let me know if it is Ok for you — Kemal AL GAZZAH, Apr 23 '19 at 14:43

Thom A · Accepted Answer · 2019-04-23T11:07:51.983

Alternatively, instead of a load of LIKE expressions, you could split the parts and inspect their lengths, and follow up by checking the string only contains digits and hyphens with a LIKE. As your string specifically has 4 parts, I've used PARSENAME here, rather than a "splitter" function.

SELECT *
FROM (VALUES ('1-1486414-305-115'),
             ('116-500-325663-1'), 
             ('116-2-2244880-1'),
             ('121-512-2623075-1'), 
             ('122-500-1944261-3'), 
             ('2-2651274-500-147'), 
             ('1-2551671-305-147'), 
             ('116-ba-2244880-1'),
             ('124-500-329130-1'))V(Code)
     CROSS APPLY (VALUES(PARSENAME(REPLACE(V.code,'-','.'),4),
                         PARSENAME(REPLACE(V.code,'-','.'),3),
                         PARSENAME(REPLACE(V.code,'-','.'),2),
                         PARSENAME(REPLACE(V.code,'-','.'),1))) PN(P1, P2, P3, P4) 
WHERE LEN(P1) != 3
   OR NOT(LEN(P2) BETWEEN 1 AND 3)
   OR NOT(LEN(P3) BETWEEN 5 AND 7)
   OR NOT(LEN(P4) BETWEEN 1 AND 2)
   OR V.Code LIKE '%[^0-9\-]%' ESCAPE '\';

Good solution ! I have also another one which uses Recursivity to split the string into many strings and check if the lenght respects the conditions, please look at my solution and let me know your thoughts — Kemal AL GAZZAH, Apr 23 '19 at 14:44

score 1 · Answer 2 · answered Apr 23 '19 at 10:46

What a pain, because SQL Server does not support regular expressions.

One method is 6 like comparisons:

where col like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9]' or
      col like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]' or
      col like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9]' or
      col like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]' or
      col like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9]' or
      col like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]' or
      col like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9]' or
      col like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9]'

Otherwise, you could count the -s, check the positions, and characters. So:

where col not like '[^-0-9]' and  -- only has digits and -
      col not like '%-%-%-%-%' and -- does not have 4 hyphens
      col like '___-___-%-%[0-9]' and -- first two hyphens in the right place and ends in digit
      '-' in (substring(col, 14, 1), substring(col, 15, 1), substring(col, 16, 1)) -- last hyphen in the right place

Kemal AL GAZZAH · Answer 3 · 2019-04-23T14:41:06.960

Here is the complete code which can achieve the required result

1) store the splitted string into a table @myvalues (I have written a solution to split a string into many rows using Recusrsivity in this Link )

2) Store the conditions in Table @tabcheck (length of each string)

3) Make a jointure between @myvalues and @tabcheck to get the result

declare @str as nvarchar(max)
set @str='116-500-325663-1';
declare @separator as char(1)
set @separator='-';


declare @tabcheck as table(id int,fromval int ,toval int)
insert into @tabcheck values(1,3,3),(2,1,3),(3,5,7),(4,1,2);

declare @myvalues as table(id int identity(1,1),myval varchar(100));
with cte as(
select @str [mystr],
cast(1 as int) [Start],
charindex(@separator,@str)as Nd
union all
select substring(@str,nd+1,len(@str)),cast(Nd+1 as int),charindex(@separator,@str,Nd+1) from cte
where nd>0
)
insert into @myvalues(myval) 
select case when nd>0 then substring(@str,start,Nd-start) 
else substring(@str,start,len(@str)) end [splitted] 
from cte   OPTION (MAXRECURSION 1000);

declare @result as int;
with mytab as(
select t1.id,t1.myval,len(t1.myval) L,t2.fromval,t2.toval,
case when len(t1.myval)>=t2.fromval and len(t1.myval)<=t2.toval then 1 else 0 end [result]
 from @myvalues t1 inner join @tabcheck t2 on t1.id=t2.id)
 select @result=count(1)  from mytab where result=0 ;

 select case @result when 0 then 'OK' else 'Mistake' end [result]

Recursion is not the best solution here I'm afraid, when you can split the item without it. This also only checks the validity (or lack of) for one value, not the entire table; making it even harder to scale. — Thom A, Apr 28 '19 at 10:59

how to check and change custom string in sql server

3 Answers3

Linked