How to check value of a VARCHAR(50) columns in SQL

Question

I am a novice user to sql and need help buidling this stored proc I am working on. One of the values that I pass through my stored proc is ClassCodeGroup and sometimes its less than 10. How do I check if its less than 10 in sql ? If its less than 10 (1-9) then I want to add a leading 0 to it which I am thinking should be something like this.

select RIGHT('0' + convert (varchar(50), @ClassCodeGroup),2)

Any help would be greatly appreciated! Thanks

Have a look here: http://stackoverflow.com/questions/121864/most-efficient-t-sql-way-to-pad-a-varchar-on-the-left-to-a-certain-length — Riv, Aug 12 '13 at 14:49
The answer here is: **Don't store numeric data in varchar columns!** — Joel Coehoorn, Aug 12 '13 at 15:11

Carsten Massmann · Accepted Answer · 2013-08-12T14:56:22.820

2

One easy way to do this would be

select RIGHT(CAST(,1000+@ClassCodeGroup AS char(8)),2)

This way you will not have to check the lenght of @ClassCodeGroup, since you simply convert a number with leading zeros and regard only the last two digits. Just changed it to CAST, since I was not sure about the CONVERT syntax ...

edited Aug 12 '13 at 14:56

answered Aug 12 '13 at 14:47

Carsten Massmann

26,510
2
22
43

Could you tell me what is ,1000+@ClassCodeGroup for ? – HereToLearn_ Aug 12 '13 at 14:52
@HereToLearn_ I add 1000 (or another large number) with at least 2 zeros to it (100 would actually be enough), so I will always end up with a number that has either leading zeros or the 2 digit number of `@ClassCodeGroup` in it. It even works when `@ClassCodeGroup` is zero. – Carsten Massmann Aug 12 '13 at 14:59

Sylvain Leroux · Answer 2 · 2013-08-12T15:16:06.907

I am a novice user to sql and need help buidling this stored proc How do I check if its less than 10 in sql

You could use IF .. THEN .. ELSE .. END IF in stored procedure and triggers:

IF @ClassCodeGroup < 10
THEN
  -- do something
ELSE
  -- do other thing
END IF;

If its less than 10 (1-9) then I want to add a leading 0

However, for that specific case, MySQL provide the LPAD function to do that exactly:

SELECT LPAD(@ClassCodeGroup, 2, '0')

Please note than would truncate values greater than 99.

Finally, and to go back to your initial question, you could use the functional IF:

SELECT IF(@ClassCodeGroup>10, 
          @ClassCodeGroup, 
          CONCAT('0',@ClassCodeGroup)
         )

Here I used the CONCAT function in order to prepend values less than 10 with a leading '0'. note with this version, values greater or equal to 10 are left unchanged.

How to check value of a VARCHAR(50) columns in SQL

2 Answers2