Help convert int to varchar function

Question

Help convert int to varchar function

adonetok

SSCrazy Eights

Points: 8395
More actions
February 27, 2014 at 7:16 am

#396342

I need to create a function to convert int to varchar.
input example: 3/12/567/8923/55668
output: 00003/00012/00567/08923/55668
Can some expert help me?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Is your string supposed to be delimited by "/" or is that just a shortcut you used to represent different records?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

djj SSCoach Points: 18831 More actions · Answer 2

adonetok (2/27/2014)
I need to create a function to convert int to varchar.
input example: 3/12/567/8923/55668
output: 00003/00012/00567/08923/55668
Can some expert help me?

If your column name was mcol

SELECT RIGHT('00000' + CAST(mcol AS VARCHAR(10)), 5) AS NewCol FROM myTable

MyDoggieJessie SSC-Forever Points: 44515 More actions · Answer 3

You could create a simple scalar function and call that as needed (or take the code from this to use as well) CREATE FUNCTION [dbo].[fx_SetStringPadding] (

@OriginalString varchar(100),

@PadToLen int,

@PadChar char(1) = ' ',

@Direction int

)

RETURNS varchar(200)

AS

BEGIN

D ECLARE @PaddedString Varchar(200)

D ECLARE @BaseLen int

SET @BaseLen = LEN(@OriginalString)

IF @BaseLen >= @PadToLen

SET @PaddedString = @OriginalString

ELSE

IF @Direction = 0

SET @PaddedString = REPLICATE(@PadChar, @PadToLen - @BaseLen) + @OriginalString

ELSE

SET @PaddedString = @OriginalString + REPLICATE(@PadChar, @PadToLen - @BaseLen)

RETURN @PaddedString

END

Example output:

SELECT dbo.fx_SetStringPadding('3', 5, '0', 0)

00003

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

MyDoggieJessie (2/27/2014)
You could create a simple scalar function and call that as needed (or take the code from this to use as well)

No, don't do that. Scalar functions are terrible for performance. They generate row by row processing, prevent parallelism, create bad plans and if used on where clauses or joins, they reduce the possibility of using indexes.

An inLine table valued function on the other side could be a good solution. If it's a single integer, you could use djj option. If you have a single string, you could use this:

DECLARE @String varchar(2000) = '3/12/567/8923/55668'

SELECT STUFF((SELECT '/' + RIGHT( REPLICATE('0', 5) + Item, 5)

FROM dbo.DelimitedSplit8K( @String, '/')

FOR XML PATH('')), 1, 1, '')

References:

DelimitedSplit8K[/url]

Concatenation using FOR XML PATH[/url]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

MyDoggieJessie SSC-Forever Points: 44515 More actions · Answer 5

Agreed, scalar function isn't the best application but the main thing I wanted to provide the OP, was some example code to use. Using Jeff's DelimitedSplit8K is a much more efficient approach. Great example!

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience