CODEWITHSUNDEEP
t-sqlsql-server-2008-r2substring
user1024008
user1024008

Reputation: 111

Substring and adding leading zeros to a string value

Hi all – I need some assistance with prefixing leading zeros into a string.

I have had a look around in the forum but did not quite find anything that suits my scenario.

The string in the column is in the following format: ‘INV-ACC-180 Some description etc’

The ‘INV-ACC-180’ bit is always the same format whilst the description can vary.

The challenge is the ‘180’. Its needs to be 4 leading zeros so it shows as 0180. i.e ‘INV-ACC-0180 Some description etc’

Some records may look like ‘INV-ACC-80 Some description etc’. And in this case the new correct format should be ‘INV-ACC-0080 Some description etc’

Many thanks in advance!

Upvotes: 0

Views: 235

Answers (1)

Conffusion
Conffusion

Reputation: 4465

Is the 180 stored as a separate value ? In that case you create 0180 using:

RIGHT('0000' + mynum, 4)

if the complete string is the input:

‘INV-ACC-' + RIGHT('0000'+substring(myinput,9,charindex(' ',myinput)-9),4) +substring(myinput,charindex(' '),1000)

Upvotes: 2

Related Questions