Concatenation check? - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Concatenation check?

Author

Topic

maniuch
Starting Member

2 Posts

Posted - 2012-01-19 : 12:40:49

Hi Guys, I hope you can help me clear my concerns on this subject.
Let’s say I have a code in [Code]that contain letters and numbers (i.e. VGBVX1AF16EXABC?) and each character is transfer to numeric value what is working fine, but while I do that ASCII transformation (Alpha to Numeric) I also want to create string from each of this operations, i.e.
A = 75 – 60 * 3
A = 45
I want to save each of the below calculation as a string not numeric value to have in example from three first letters of my code above a string of data - VGB where V = 16, B=5, C=75 I don’t want then 16+5+75 = 96 I need value of 16575

update dbo.POS_01
set [Code]=substring([Code],1,15)+
cast(
(((

(select dbo.sumdigits(((ascii(substring([Code],1,1))-60)*3)))+
(select dbo.sumdigits(((ascii(substring([Code],2,1))-60))))+
(select dbo.sumdigits(((ascii(substring([Code],3,1))-60)*5)))+
)))
as varchar(1))
from dbo.POS_01
where
left([Code],2)='VG'

when I do update above select is giving me actually value of 96 not 16575.
How to make working? Any help is always appreciated.

select (
(((ascii(substring([Code],1,1))-60)*3))+
(((ascii(substring([Code],2,1))-60)))+
(((ascii(substring([Code],3,1))-60)*5))+
)from dbo.POS_01
where
left([Code],2)='VG'
and
[ID] = '10032'
Mark

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-19 : 12:55:04

Hello Mark,

It would appear that the values are being treated as numeric and being aggregated. Perhaps you could CAST each of them as VARCHAR as you SELECT them.

HTH.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-19 : 14:04:09

The ASCII function returns an INT. SO, when you add (+) INTs you get an INT. If you need to concatenate the values, then you need to do as ehorn has suggested and CAST or CONVERT the values to the proper datatype.

maniuch
Starting Member

2 Posts

Posted - 2012-01-20 : 06:56:01

Hi Guys

Sorry for my English ;)

Thank you for your help, I appreciate your suggestions, they make me thinking, I didn’t want to do case for each digits like below – actually I didn’t make this working anyway, possible some brackets.

select
case ((((ascii(substring([Code],1,1))-60)*2)))
when <= 9 then (select (((ascii(substring([Code],1,1))-60)*2)))
else
left(CAST(((((ascii(substring([Code],1,1))-60)*2))) as varchar(2)),1)+
RIGHT(CAST(((((ascii(substring([Code],1,1))-60)*2))) as varchar(2)),1)
end
+
case ((((ascii(substring([Code],2,1))-60)*2)))
when <= 9 then (select (((ascii(substring([Code],1,1))-62)*2)))
else
left(CAST(((((ascii(substring([Code],1,1))-60)*2))) as varchar(2)),1)+
RIGHT(CAST(((((ascii(substring([Code],1,1))-60)*2))) as varchar(2)),1)
end
from POS_01

I did something different as requirement was to ,,encrypt,, results by mod10 algorithm, this is why I was looking for values not being added together but separate digits, example from my first post
16+5+75 = 96 I need value of 16575
96 is incorrect as I need 16 5 75 where 16 will be 1+6, 5 is 5, 75 will be 7+5 what will give me sum of 1+6+5+7+5 = 24 not 96

I know it is little complicated from the way I am trying to explain.

I had an error in my first post as I used stores function and I didn’t use this function in select statement, my script was working fine ,,in this place,, but error is still somewhere else but select statement I did was wrong and lead me in to wrong direction to find a problem.

Function that is sorting above calculations and is allowing me to have string of digits that I can simple add all together later is below.

ALTER FUNCTION [dbo].[sumdigits] (@number int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @ctr int
SET @ctr = 0

WHILE @number >= 10
BEGIN
SET @ctr = @ctr + (@number % 10)
SET @number = @number / 10
END

RETURN @ctr + @number
END;

This is how the select statement should looks like if I need 16 5 75

select dbo.digits((ascii(substring([Voucher Code],1,1))-60)*2),
dbo.digits((ascii(substring([Voucher Code],2,1))-60)),
dbo.digits((ascii(substring([Voucher Code],3,1))-60)*2)
from dbo.POS_01

Below statement which will give me 1+6+5+7+5 = 24instead

select dbo.digits((ascii(substring([Voucher Code],1,1))-60)*2)+
dbo.digits((ascii(substring([Voucher Code],2,1))-60))+
dbo.digits((ascii(substring([Voucher Code],3,1))-60)*2)
from dbo.POS_01

Thank guys for a help, still lots of to learn for me.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources