int to char conversion - SQL Server Forums

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

int to char conversion

Author

Topic

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-08 : 08:38:05

Hi,

I'm scrambling to complete a project and ran into an int to char problem. I'm sure, in my haste, I'm doing something stupid. Here is an example of what I'm trying to do but it doesn't convert as expected. I want the SEQ_NBR2 value to convert from 4 to 0000004.

select right('0000000' + convert(char(7), isnull(a.SEQ_NBR2, 0.0)), 7)
from TXN_HEAD_837 a
where a.file_auth_nbr = '1084472388468'
and a.seq_nbr2 = 4
and a.sgmt_id2 = 'BHT'

Thanks, Jeff

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-08 : 08:52:02

select right('0000000' + convert(varchar(7), isnull(a.SEQ_NBR2, 0.0)), 7)

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-08 : 08:57:58

Thanks very much KH I will try that !!
Jeff

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-08 : 09:09:57

Hmmm...changing the select to:
select right('0000000' + convert(varchar(7), isnull(a.SEQ_NBR2, 0.0)), 7) did not work - still getting just the value 4 not 0000004.
Thanks,
Jeff

JeffT
Posting Yak Master

111 Posts

Posted - 2006-05-08 : 11:32:14

Found my problem. The "select right('0000000' + convert(varchar(7), isnull(a.SEQ_NBR2, 0.0)), 7)" DID work after all, I messed something up.

Thanks again KH !
Jeff

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-08 : 14:15:43

Since you are using SEQ_NBR2 in the Where clause you don't need to use the ISNULL function, which will just slow things down. If the value is NULL it won't be selected.

Secondly, if I had to maintain this command down the road I'd probably be confused as to why you you used 0.0 if the field is normally an INT, just use 0 as the value if the value was NULL.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources