CODEWITHSUNDEEP
sqlsql-server
goofyui
goofyui

Reputation: 3492

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

Upvotes: 0

Views: 3283

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use a check constraint. You seem to want something like this:

alter table t add constraint chk_name as
    (name like '%,%' and
     name not like '%,%,%' and
     name not like '%[^a-zA-Z,]%'
    )

SQL Server doesn't have support for regular expressions. This implements the rules:

  • Name has to have a comma
  • Name does not have two commas
  • Name consists only of alphabetic characters and a comma

You may find that you need slightly more flexibility, but this handles the cases in your question.

Upvotes: 1

Related Questions