goofyui goofyui - 2 years ago 73
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

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download