SQL Validating a string variable

Question

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

What exactly do you mean by "validate from SQL"? Do you need an SQL that returns all invalid values? Or all valid ones? Or an extra field indicating whether the name is valid or not? — Heinzi
– Heinzi, Commented Jun 11, 2016 at 19:17

Alex Kudryashev · Accepted Answer · 2016-06-11 19:52:37Z

1

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.

answered Jun 11, 2016 at 19:52

Alex Kudryashev

9,5103 gold badges31 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

goofyui Over a year ago

Your syntax logic helps me. This resolves the data issue, I can tweak it according to my needs. Thanks a lot !!

goofyui Over a year ago

Will you please help me in this posting : stackoverflow.com/questions/37766643/…

Gordon Linoff · Accepted Answer · 2016-06-11 16:51:46Z

1

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.

answered Jun 11, 2016 at 16:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

goofyui Over a year ago

i cant alter the table. I have to work on the data part. database belongs to third party.

Gordon Linoff Over a year ago

@goofyui . . . You can use the same logic in a where or case. Your question states that you want to validate data and that is what a check constraint does.

goofyui Over a year ago

Thank you Gordon, Not sure whether i was able to get it. I tried with sample query, it didnt work. I have posted a Sample Temp table query in the original posting. Please take a look.

Collectives™ on Stack Overflow

SQL Validating a string variable

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related