Changing the size and type of a field for output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Changing the size and type of a field for output

Author

Topic

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-30 : 19:09:27

Is there a way to take an integer field and output as an 8 digit char field in a select statement? The field that I want to select i ivh_hdrnumber (int,null) and I want the output to be char(8)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-30 : 19:37:26

It would be best if you output the int field from the database and then formatted it to whatever you wanted in your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-30 : 19:48:50

If you REALLY WANT to do it in SQL:


declare @tv table
(
	ivh_hdrnumber int NULL
)

insert @tv
select 7 union all
select 52 union all
select 5923 union all
select 12345678 union all
select 1999999988 

select 
	STUFF(convert(char(8), '00000000'), (8 - len(ivh_hdrnumber))+ 1 , len(ivh_hdrnumber), left(cast(ivh_hdrnumber as varchar(8)), len(ivh_hdrnumber))) AS ivh_hdrnumber
from @tv

Output:

ivh_hdrnumber
--------------
00000007
00000052
00005923
12345678
NULL

Note that the last entry is null - that's what happens if you have a number larger than 8 digits.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-30 : 20:13:14

And just remember that just because you *can* do it at the database layer, doesn't mean that you should do it there. The application should handle things like this. Return raw data from SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-30 : 20:17:30

Thanks I really do need to learn more

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-30 : 23:53:33

try this

another method to get same output which tonymorell10 as suggested

declare @tv table
(
ivh_hdrnumber INT NULL
)

insert @tv
select 7 union all
select 52 union all
select 5923 union all
select 12345678 union all
select 1999999988

SELECT
CASE
WHEN LEN(ivh_hdrnumber) < 9 THEN RIGHT('00000000'+CAST(ivh_hdrnumber AS VARCHAR(30)),8)
WHEN LEN(ivh_hdrnumber) > 8 THEN NULL
END AS ivh_hdrnumber
FROM @tv

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources