CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Köttur
Köttur

Reputation: 19

T-SQL function to convert int to varchar with plus/minus sign

While I've been troubled by annoying SQL operations, I've got another T-SQL problem. I have to convert some Int variables into zero-filled varchar with plus/minus sign.

I have tried RIGHT() and CONVERT() functions so far, 

RIGHT('000000' + CONVERT(value1 AS VARCHAR(6)), 6)

but I couldn't figure out what the best way to convert them.

What I would like to see is:

If value1 = 305 then  '+000305'

If value1 = -110 then '-000110'

Is there any simple function to achieve this? Or should I have to create my own function for this? I know it's stupid question, but my current task requires a lot of such non-sense tasks to Stored Procedures (database-side) rather than 'application-side'.

Thanks.

Upvotes: 0

Views: 1043

Answers (3)

Stephan
Stephan

Reputation: 6018

For SQL 2012 and above, you can use FORMAT().

DECLARE @yourTable TABLE (nums INT);
INSERT INTO @yourTable VALUES (305),(-110);

SELECT  RIGHT('+' + FORMAT(nums,'00000#'),7) AS formatted_nums
FROM @yourTable

Results:

formatted_nums
--------------
+000305
-000110

Upvotes: 1

mohan111
mohan111

Reputation: 8865

may be this one can achieved in both ways using REPLACE or Replicate function.

DECLARE @t TABLE (Num INT)

INSERT @t
SELECT 305
UNION SELECT -110

SELECT CASE WHEN Num < 0 THEN '-' ELSE '+' END +
replace(RIGHT('00000'+ CONVERT(VARCHAR,Num),8),'-','0') AS NUM 
FROM @t

SELECT CASE WHEN Num < 0 THEN '-' ELSE '+' END +
RIGHT(Replicate('0',6) + CONVERT(VARCHAR,Abs(Num)), 6)
FROM @t

Upvotes: 0

Brian Pressler
Brian Pressler

Reputation: 6713

You want to use CAST instead of CONVERT when you structure it that way. There's no standard function that will do what you want, but you could create an expression like this to get what you need:

LEFT(REPLACE(SIGN(value1),'1','+'),1) + RIGHT('000000' + CAST(ABS(value1) AS VARCHAR(6)), 6)

If you want to use CONVERT it would look like:

LEFT(REPLACE(SIGN(value1),'1','+'),1) + RIGHT('000000' + CONVERT(VARCHAR(6),ABS(value1)), 6)

Just for fun, here's another way you could do it:

LEFT(REPLACE(SIGN(value1),'1','+'),1) + REPLACE(STR(ABS(value1),5),' ','0')

Upvotes: 2

Related Questions