Integer lowest order 2

[IAMINFO]

Hello everyone ,
I have a number 80505 how would I use sql to show the number as 00558, not really sure where to start.

Thank you for taking time to read this post.

 

[gmmastros]

I would approach this by splitting the string in to rows, then sort, and concatenate back to a string, like this:

Declare @Test VarChar(20)

Set @Test = '80505'
Declare @Output VarChar(20)


-- Prepare a table variable to hold the digits
Declare @Digits Table(Digit Char(1))

-- Insert the digits in to the table variable
Insert Into @Digits
Select	SubString(@Test, Number, 1) As Digit
From	master..spt_values
Where	Type = 'P' 
		And Number Between 1 And Len(@Test)

-- Concatenate the data in the table variable
-- in to a single string
Set @Output = ''

Select	@Output = @Output + Digit
From	@Digits As Digits
Order By Digit

Select @Output

How large can your number be?

Will you need to do this for a single number or will you need to apply this to multiple rows?

What is the data type for the input? Is it a varchar or an integer?

The reason I ask is because there may be a better (faster) way if you are limited to a 5 digit number.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[IAMINFO]

gmmastros thank you very much, I need to apply this to multiple rows, the data type is an integer. Thank you for helping me.

 

[markros]

See my reply in your other thread. BTW, I didn't see George's reply when I answered, but our approaches to this problem have been identical - split first, then concatenate.

PluralSight Learning Library

 

[gmmastros]

If you have a relatively small range of values (1 million or less), I would encourage you to create a numbers table with your calculated value as a second column so it is a simple join/lookup.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom