adding a number as string to the end of a value

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

adding a number as string to the end of a value

Author

Topic

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-28 : 14:48:28

Hi,

I have a following table, I want to add at the end of the string in naics coulumn '11' when its length is = 6, but it just adds 11 to the end of the number not increases the length:

SELECT [hs]
,naics
,naics8_97 = CASE
When len(naics)> 6 then naics
when len(naics)<= 6 then naics +'11'
else null
end

FROM [trade].[dev].[hs2naics8_import_2007]

for example if I have nacis = 451277 I would like to make naics8_97 =45127711 but it returns 451288

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-10-28 : 15:07:28

Use cast or convert:

...
...then cast(naics as varchar(20)) + '11'

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 15:12:34

I'm assuming it's a number




DECLARE @naics int; SET @naics = 451277

SELECT
  @naics
, CASE WHEN len(@naics)> 6 THEN CAST(@naics AS varchar)
						   ELSE CAST(@naics AS varchar) +'11'
	   END AS naics8_97

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

goligol
Posting Yak Master

128 Posts

Posted - 2011-10-28 : 15:32:42

thank you, thank you

X002548
Not Just a Number

15586 Posts

Posted - 2011-10-28 : 16:19:57

Yet you still don't cast the number to varchar...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources