Concatenating to String Problem

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

Concatenating to String Problem

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-19 : 07:24:50

Hi

I am trying to concatenate a value to a string with numbers in it, in a function, and cannot concatenate the value '0' to the start of the string but every other number value works. Why is this?

Like this:

set @str = ('0' +str)

So say the number was 34 I would want to return 034 but it won't concatenate the string value '0'

The data type is set to return Numeric so is [043] not a numeric value?

It will return any other number bar 0 concatenated onto it.

thanks

G

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 07:28:32

Correct 043 is not numeric,it's a string, so it's knocking off the leading 0's. Change @str,str to varchar

Jim

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-19 : 07:57:34

Hi

Not exactly sure what bit you mean to change. I want to change a string value to remove all character values and then return the number left over as a number value; got this bit ok. There is only a maximum of three digits that will be left over and no decimal places or anything like that.

So looking at the full code below what do I have wrong?

CREATE FUNCTION dbo.NumbersFromString (@str varchar(100))  --Value is originally a string
RETURNS NUMERIC AS  
BEGIN 

WHILE PATINDEX('%[^0-9]%', @str )> 0 -- Determines if there is any characters in the string, the number returned is where the first charcter in the string is identified
set @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^0-9]%', @str),1),'')       -- Replace values other than numbers with  nothing.
if LEN(@str) = 2   --Will also add code for value length = 1
set @str =  ('0' +@str) -- Add string '0' to existing string value
if @str = ''            -- handles if string has no numbers in it
set @str = '0'
return CAST( SUBSTRING(@str, 1, 3) AS Numeric) -- Change type to number
END

Thanks

G

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 08:05:21

Your query is doing exactly what you want it to. It will turn '43' to '043' here
set @str = ('0' +@str) -- Add string '0' to existing string value

and then turn it back to 43 here
return CAST( SUBSTRING(@str, 1, 3) AS Numeric).

You can't have it both ways. Either return a string of '043' or a numeric of 43. Or even a string of '43', but you can't return a numeric of 043.

Jim

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2009-06-19 : 08:19:36

So like this?

CREATE FUNCTION dbo.NumbersFromString (@str varchar(100))  --Value is originally a string
RETURNS varchar(10) AS  
BEGIN 

WHILE PATINDEX('%[^0-9]%', @str )> 0 -- Determines if there is any characters in the string, the number returned is where the first charcter in the string is identified
set @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^0-9]%', @str),1),'') -- Replace values other than numbers with nothing.
if LEN(@str) = 2 
set @str =  ('0' +@str)
if @str = ''  
set @str = '0'
return @str
END

This now works ok so far. It should order ok now - hopefully!

Thanks

G

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources