goofyui goofyui - 5 months ago 10
SQL Question

SQL Validating a string variable

Using SQL Scripts, I need to validate Comma Separate value. How should i validate the String Variable ?

Validation should be both Right / Left Trim for each value and there should not be any special characters such as Comma or Period for the last value.

create table #test
(col varchar(100))

insert into #test values
('1,2'),
('1,2,'),
('1,'),
('1,2,3,4,5')


select * from #test


In the above query, for the second value - Expected Result is 1,2
In the above query, for the Third value - Expected Result is 1

Answer

You can update your table to fix "offensive" values.

update #test
set col = substring(col, 1, len(col) - 1)
where col not like '%[0-9]'

This will remove last character where value doesn't end by a digit.

Comments