Changing data when alpha characters are present.

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 2005 Forums

Transact-SQL (2005)

Changing data when alpha characters are present.

Author

Topic

rtpnc
Starting Member

19 Posts

Posted - 2007-08-23 : 14:14:08

We hava a table that is updated every night. There are many fields WKT04 WKT09 that have (100 or less) records with data like this 0000000085Q
and 0000000049M. Their people told me that a Q = 8, M = 4 and the alpha indicates that the complete number is negative. so 0000000085Q
= -858 and 0000000049M = -494.

Complete Alpha List:
J -1
K -2
L -3
M -4
N -5
O -6
P -7
Q -8
R -9
} -0

We have to SUM WKT04 and WKT09. So we would have to make the column int. What sql would we write to accomplish this?

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 14:29:58

I remember this one.
A time ago, me and Rockmoose helped out a (norwegian) guy with a function to calculate the value.
It was something about importing data from legacy system.

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 14:34:25

Is the alpha value always going to be at the end?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rtpnc
Starting Member

19 Posts

Posted - 2007-08-23 : 14:47:31

Yes, it will always be at the end.

Apparently this came off an AS400. Basic IBM stuff i was told.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 14:49:31

Create the function first

CREATE FUNCTION dbo.fnCalcValue
(
	@Item CHAR(11)
)
RETURNS INT
AS
BEGIN
	RETURN	'-' + LEFT(@Item, 10) + CONVERT(CHAR, (ASCII(UPPER(RIGHT(@Item, 1))) - 21) % 52)
END

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 14:50:14

Nu testar vi den!

declare	@sample table (data varchar(50), realvalue int)

insert	@sample
select	'0000000085Q', -858 union all
select	'0000000049M', -494

select		data,
		realvalue,
		dbo.fnCalcValue(data) AS theCalculatedValue
from		@sample

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 14:56:32

Another way:


Declare @t table (rowid int identity, keycol char(1), value int)
insert into @t 
Select 'J' ,-1 union all
Select 'K' ,-2 union all
Select 'L' ,-3 union all
Select 'M' ,-4 union all
Select 'N' ,-5 union all
Select 'O' ,-6 union all
Select 'P' ,-7 union all
Select 'Q' ,-8 union all
Select 'R' ,-9 union all
Select '}' ,-0

Declare @num Table (data varchar(20))
Insert into @num values ('0000000085Q')
Insert into @num values ('0000000049M')


Select data , Converteddata= convert(int,replace(data, right(data,1), (Select value from @t Where keycol = Right(data,1)) * -1)) * -1
from @num

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 14:59:52

And solving the OP's first wish, summing up several columns?
This is very easy with my inline scalar function

SELECT PkCol, SUM(dbo.fnCalcValue(WKT04) + dbo.fnCalcValue(WKT05) + dbo.fnCalcValue(WKT06) + dbo.fnCalcValue(WKT07) + dbo.fnCalcValue(WKT08) + dbo.fnCalcValue(WKT09))
FROM Table1
GROPU BY PkCol

E 12°55'05.25"
N 56°04'39.16"

rtpnc
Starting Member

19 Posts

Posted - 2007-08-23 : 15:49:10

I could not get the sql to work. We dont need to sum up the columns in the database. We do this on the site itself.

Also the table name is tblCReal.

Here is a shot of data.

2000	1000171	0000000085Q	858	MONTHLY INTEREST	00000000000
2001	1000171	0000000049M	494	MONTHLY INTEREST	00000000000
2001	1000171	0000000049M	494	MONTHLY INTEREST	00000000000
2002	1000171	0000000032M	324	MONTHLY INTEREST	00000000000
2002	1000171	0000000032M	324	MONTHLY INTEREST	00000000000
2004	1186402	0000000110O	1106	MONTHLY INTEREST	00000000000

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 15:59:29

[code]
Declare @t table (rowid int identity, keycol char(1), value int)
insert into @t
Select 'J' ,-1 union all
Select 'K' ,-2 union all
Select 'L' ,-3 union all
Select 'M' ,-4 union all
Select 'N' ,-5 union all
Select 'O' ,-6 union all
Select 'P' ,-7 union all
Select 'Q' ,-8 union all
Select 'R' ,-9 union all
Select '}' ,-0

Declare @num Table (data varchar(20))
Insert into @num values ('0000000085Q')
Insert into @num values ('0000000049M')
Insert into @num values ('0000000032M')
Insert into @num values ('0000000110O')

Select data , Converteddata= convert(int,replace(data, right(data,1), (Select value from @t Where keycol = Right(data,1)) * -1)) * -1
from @num

----------------
data Converteddata
-------------------- -------------
0000000085Q -858
0000000049M -494
0000000032M -324
0000000110O -1106
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 16:18:02

One set of paranthesises less!

CREATE FUNCTION dbo.fnCalcValue
(
	@Item CHAR(11)
)
RETURNS INT
AS
BEGIN
	RETURN	'-' + LEFT(@Item, 10) + CONVERT(CHAR, ASCII(UPPER(RIGHT(@Item, 1))) % 21 % 10)
END

E 12°55'05.25"
N 56°04'39.16"

rtpnc
Starting Member

19 Posts

Posted - 2007-08-23 : 16:19:45

dinakar,

Thanks for helping us, but we are having problems applying your logic to our database. We see how you create this temp table but cannot understand what we need to apply or not apply for it to work with our table. What sql do we need to write.

We also want to update the column but not create a new column when we do this.

Thanks for all the help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 16:22:45

SELECT Col1, Col2, Col3, dbo.fnCalcValue(Col3), ABS(dbo.fnCalcValue(Col3)), Col4, Col5
FROM tblCReal

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 16:24:18

Just keep ignore the function approach...

E 12°55'05.25"
N 56°04'39.16"

rtpnc
Starting Member

19 Posts

Posted - 2007-08-23 : 16:28:19

Peso,

Thanks we are not ignoring your approach, I forgot to add your name to the above post :(

Thanks for the help!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-23 : 16:30:25

quote:
Originally posted by Peso

I remember this one.
A time ago, me and Rockmoose helped out a (norwegian) guy with a function to calculate the value.
It was something about importing data from legacy system.

E 12°55'05.25"
N 56°04'39.16"

Looks like data from a COBOL based system where the right column is overloaded to contain the sign.

CODO ERGO SUM

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-23 : 16:35:59

quote:
Originally posted by rtpnc

dinakar,

Thanks for helping us, but we are having problems applying your logic to our database. We see how you create this temp table but cannot understand what we need to apply or not apply for it to work with our table. What sql do we need to write.

We also want to update the column but not create a new column when we do this.

Thanks for all the help.

The temp table is simply a look up table for a list of codes and its corresponding replacement value. You can add/remove/modify the look up values without having to change your code. Consider it like a states look up table where you have state code like CA and you get the full name of California from the lookup table.

The SELECT is just to verify if the values that you are getting out of the expression/manipulation is exactly what you are expecting it to be. Once you are sure, you can replace the SELECT with UPDATE with appropriate syntax changes.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 19:31:37

I have done some investigation, and last character is not always alphanumeric.
It is only alphanumeric for negative values.

See this topic http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88357

E 12°55'05.25"
N 56°04'39.16"

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2007-09-20 : 15:28:44

This really takes me back.....

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9678

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-20 : 15:34:05

quote:
Originally posted by Peso

I remember this one.
A time ago, me and Rockmoose helped out a (norwegian) guy with a function to calculate the value.
It was something about importing data from legacy system.

E 12°55'05.25"
N 56°04'39.16"

MOOOOOOOOOOOOOOOOOOOOSE

Where the heck has he been?

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

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources