CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Denis1893
Denis1893

Reputation: 65

Add leading zeros after specific character

I have the following problem (Example Strings):

10162/0 should look like 010162/0000
47000/685 should look like 047000/0685
9055/2 should look like 009055/0002

So up to the '/', I need 6 digits with leading zeros if there are not exactly 6 digits. And after the '/' I need exactly 4 digits with leading zeros if there are no 4 digits.

I have tried it with:

SELECT RIGHT('000000/0000' + [column_name], 11) ...

but of course, it isn't working cause of the second part after the '/' :)

I hope anyone can help me. Thanks in advance!

Upvotes: 0

Views: 143

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can do this with a bunch of string manipulations. Something like this:

select right(replicate('0', 6) + left(val, charindex('/', val)), 7) +
       right(replicate('0', 4) + right(val, charindex('/', reverse(val)) - 1), 4)

Unfortunately, SQL Fiddle just isn't returning this morning, so I can't fully test this.

EDIT: Fixed missing parenthesis

Upvotes: 2

Related Questions