Convert int to Formated String

[MichaelaLee]

Hi Everyone,
I have a need to convert an int field to a string. For example, if the int field contains 39, I need to the output to look like "NF00039". Actually what I'm doing is using a support table to get the next Key value and that returns an Int. Then I need to take that value and convert it as above then add a new record to the main table using the formatted string as the key. Thanks for any help.
Michael Lee

 

[NoCoolHandle]

select 'N0000' + cast(39 as varchar(3000))

look into the len and replicate functions.. that should allow you to format the number of 0's you need between the N and the 39

HTH

rob

 

[cLFlaVA]

Here's a way:

[tt]
SELECT 'NF' + RIGHT(('00000' + CAST(col_name AS nvarchar)), 5)
FROM table_name
[/tt]

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[MichaelaLee]

Thanks for the replies. I went back and searched deeper and found that I could use the following code that worked:
Set @LastValue = (Select ProductId from ProductIdSupport)
set @LastValue = @LastValue + 1
Set @StrValue = 'NFM' + Replicate('0', 4 - len(convert(varchar(10),@LastValue))) + convert(varchar(10),@LastValue)

Thanks again for the replies. Have a great day.
Michael

 

[cLFlaVA]

Why use variables with three lines of code, when 1 select statement will suffice?

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[NoCoolHandle]

clflava - you are 100% correct...

I must admit, I looked at your solution and thought it looked so nice, that I might use it in some situations where I have take the other direction. Of course that requires a bit of a re-write but I think it is worth the imporvements..


simple is always best!

(i kind of assumd that MichaelaLee would look at your code and realize the same... oh well)

 

[cLFlaVA]

Thanks

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.