-1

I have a requirement as below:

SELECT code from Products
  • Select code to table Products
  • Check length of code; if < 8 auto insert "0" before it to enough 8 character.

Sample:

If code is : 1234  => 00001234
             12345 => 00012345
             ..... => xxxxxxxx

I want to use a stored procedures in SQL Server 2012 to do it.

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
user2024503
  • 44
  • 7
  • You can refer to this [link](http://stackoverflow.com/questions/19947878/sql-server-how-to-create-a-stored-procedure) on how to create a stored procedure and to this [link](http://stackoverflow.com/questions/16760900/pad-a-string-with-leading-zeros-so-its-3-characters-long-in-sql-server-2008) on how to pad a string with leading zeros up to certain character long. – tjeloep Dec 19 '14 at 01:57
  • You should be able to do this as an `UPDATE` statement, or even changing it into a trigger. Although I'm more worried about the fact that you're prepending zeros - it likely means there's something screwy with how you're using this field... – Clockwork-Muse Dec 19 '14 at 06:34

3 Answers3

6

If the CODE field is string, this will work

SELECT RIGHT('00000000'+ISNULL(CODE,''),8) from Products

If CODE is integer then 

 SELECT RIGHT('00000000'+CAST(CODE AS VARCHAR(8)),8) from Products
HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
0
SELECT
    CASE
        WHEN LEN(code) < 8 THEN REPLICATE('0', 8 - LEN(code)) + code
        ELSE code
    END
FROM Products
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
0

Do something like this.

  1. Use function "CONCAT" your numbers with 000000000 .
  2. Use function "RIGHT" to show 8 digits from right to left .

select right(CONCAT('000000000', 1234),8) as xxx

Hope its help.

Suteerapan Panyaduang
  • 11
  • 1