Remove leading zero in a temp variable

Question

I have declalared a temp variable in sql. it contains a string A001, A011, A002, A14, A158.

i want to remove zeroes to get following output

A1, A2, A11, A14, A158

do all of your values have a single alpha character before the numeric part? — Tanner, Nov 26 '14 at 11:45
possible duplicate of [Remove zero in a column](http://stackoverflow.com/questions/27145714/remove-zero-in-a-column) — Neeraj Prasad Sharma, Nov 26 '14 at 12:30
Not the exact duplicate. Here the data is not in table form. It is a string — Veera, Nov 26 '14 at 12:32

Tanner · Answer 1 · 2014-11-26T11:56:06.700

You can achieve what you're after with some string manipulation.

So if you take the first character from A010:

DECLARE @SomeValue nvarchar(10) = 'A010'

SELECT LEFT(@SomeValue, 1) -- gives you 'A'

Then get the numeric part, by removing the first character and convert it to an integer:

SELECT CONVERT(INT, SUBSTRING(@SomeValue, 2, LEN(@SomeValue) - 1)) -- returns 10

This will remove the leading zeroes for you. You then just need to convert it back to a string and append it on to the first character.

Full working solution:

DECLARE @T1 TABLE ( SomeValue NVARCHAR(10) )

INSERT  INTO @T1
        ( SomeValue )
VALUES  ( 'A001' ),
        ( 'A011' ),
        ( 'A002' ),
        ( 'A14' ),
        ( 'A158' ),
        ( 'A010' )

SELECT  SomeValue AS Original ,
        LEFT(SomeValue, 1)
        + CONVERT(NVARCHAR(10), 
          CONVERT(INT, SUBSTRING(SomeValue, 2, LEN(SomeValue)-1))) RemoveLeadingZeroes
FROM    @T1

What he is asking for if to change single string A001, A011, A002, A14, A158 to A1, A2, A11, A14, A158. Not a table of content. — Veera, Nov 26 '14 at 12:30
My mistake, I read it as I have a temp table with these values. — Tanner, Nov 26 '14 at 13:50

Veera · Accepted Answer · 2014-11-26T12:28:58.727

-1

Use this: (I used XML to replace extract each word and then replaced the 0's by casting 0010 to 10 and combined the values A + 10 to get A10 and again combined the each word to form A1, A2, A14..)

DECLARE @string VARCHAR(MAX),

@Split CHAR(1),
@X xml

SELECT @string = 'A001, A011, A002, A14, A158',
@Split = ', '

SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@string,@Split,'</s><s>') + '</s></root>')

SELECT 
 STUFF(((SELECT ', ' + CAST(Result AS VARCHAR(100)) FROM 
    (SELECT SUBSTRING(LTRIM(Result), 1, 1) + 
           CAST(CAST(SUBSTRING(LTRIM(Result), 2, LEN(LTRIM(Result))) AS INT) AS VARCHAR) AS Result 
     FROM
         (SELECT T.c.value('.','varchar(max)') AS Result
          FROM @X.nodes('/root/s') T(c)) AS NewResult) 
    AS NewResult1
FOR xml path(''), type).value('.','varchar(max)')), 1,2,'')
AS FinalResult

SqlFiddle: Demo

edited Nov 26 '14 at 12:28

answered Nov 26 '14 at 11:40

Veera

3,412
2
14
27

it removes all zeroes from a string for A010 I want result A10 but it print A1 – user2148124 Nov 26 '14 at 11:46
I have following string A001, A011, A002, A14, A158, A010 and want following output A1, A11, A2, A14, A158, A10 – user2148124 Nov 26 '14 at 12:03
@user2148124, The answer will let you to exact result. Do check it. – Veera Nov 26 '14 at 12:23
Hi @user2148124 if this or any answer has solved your question please consider [accepting it](http://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. – AHiggins Nov 26 '14 at 13:15
@Veera it does since you edited it. I commented on the previous version of the answer. – Tanner Nov 26 '14 at 13:51

Remove leading zero in a temp variable

2 Answers2