Replace last character in a string

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)

Replace last character in a string

Author

Topic

lromano1962
Starting Member

13 Posts

Posted - 2007-10-23 : 16:35:35

I am trying to replace the last character in a string using SQL query, or in an update query in Access. Either one will do. Thanking you in advance!

Example1: 0000000A to 0000001
Example2: 0000005H to 0000058

Lisa

Lisa Romano

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-23 : 16:45:03

You can do something like this: SELECT REPLACE('0000000A', RIGHT('0000000A',1), '1')

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

lromano1962
Starting Member

13 Posts

Posted - 2007-10-23 : 16:49:00

I want to do this for every row in the field and each one is different.

Lisa Romano

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-23 : 16:51:42

If there is a pattern like all 'A's to 1's etc you can do a batch update. Does a pattern exist?

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

lromano1962
Starting Member

13 Posts

Posted - 2007-10-23 : 16:53:02

Yes, A=1, B=2, C=3.. What would the syntax be for a batch update?

Lisa Romano

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-23 : 16:54:04

Pattern is:
A = 1
B = 2
C = 3
ect

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-23 : 17:20:10

What about J? That maps to 10 based on your pattern; that is no longer replacing a single character with another single character.

- Jeff
http://weblogs.sqlteam.com/JeffS

lromano1962
Starting Member

13 Posts

Posted - 2007-10-24 : 09:27:09

Couuld you please explain what you mean by J and your method? Thanks so much!!

- Lisa

Lisa Romano

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-24 : 09:32:25

quote:
Originally posted by lromano1962

Couuld you please explain what you mean by J and your method? Thanks so much!!

- Lisa

Lisa Romano

How do you want to replace this?
0000000J

Madhivanan

Failing to plan is Planning to fail

lromano1962
Starting Member

13 Posts

Posted - 2007-10-24 : 09:40:33

Just to give you a little more detail. I have an existing table and one of the fields (Billed Charge) had text imported into the field from an outside source. When the information was imported apparently they imported the last character as a hex. I want to convert the last character of the field so that I can calculated the field in Crystal Reports. So, every entry is different. The first 6 characters in the field are numeric and the last character is a { or A,B,C,D,E,F,G,H & I. The field in the table is text, so once I convert the last character, I have no problem with changing it to numeric. I would like a simple sql syntax to do this or I can use an access update query as well. Either one will do. Thanks!!!

Lisa Romano

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 09:50:53

Is your data originally from COBOL?

Have a look at this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88357

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-24 : 09:51:37

Not sure why you want to replace with 1 to 9

Try

SELECT REPLACE(col, RIGHT(col,1), ASCII(RIGHT(col,1))-64) from table

Madhivanan

Failing to plan is Planning to fail

lromano1962
Starting Member

13 Posts

Posted - 2007-10-24 : 09:52:18

I'm not sure where it came from.. It was in the table when I got a hold of the data. Right now it's in a SQL Table and also their is a link in Access.

Lisa Romano

lromano1962
Starting Member

13 Posts

Posted - 2007-10-24 : 09:55:43

The replacement of 1-9 is what the numeric should be in the charge.
Example: anything that has an a is 1 - 0000019 like .19 cents.

In that syntax is the col the field name, if not how do i specify which field i am doing the replacement on?

Lisa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 10:00:32

I believe not all data ends with a character. Am I right?
In COBOL conversion, when data ends with character it means that the number is negative too.

Please be sure you do not accidentally update something which will give you wrong value later.

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

lromano1962
Starting Member

13 Posts

Posted - 2007-10-24 : 10:04:07

Yes, all data end with a character. I have check several records and they are all positive numbers. Thanks for pointing this out to me.

Lisa Romano

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 10:04:59

Try this

CREATE FUNCTION [dbo].[fnLisa2Int]
(
	@Item VARCHAR(18)
)
RETURNS BIGINT
AS
BEGIN
	RETURN	CAST(
			CASE
				WHEN @Item LIKE '%[^{abcdefghi0123456789ABCDEFGHI]%' THEN NULL
				WHEN LEFT(@Item, LEN(@Item) - 1) LIKE '%[{abcdefghiABCDEFGHI]%' THEN NULL
				ELSE ''
			END + LEFT(@Item, LEN(@Item) - 1) + CHAR(47 + CHARINDEX(RIGHT(@Item, 1), '{ABCDEFGHI0123456789{abcdefghi') % 10) AS BIGINT)
END

SELECT *, dbo.fnLisa2INT(Col2) FROM Table1

to see if it gives the values you want.

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

lromano1962
Starting Member

13 Posts

Posted - 2007-10-24 : 10:07:20

Opps.. Let me rephrase my example:

The replacement of 1-9 is what the numeric should be in the charge.
Example: anything that has an a is 1 - (Before) 000001A (After) 0000091 like .91 cents.

In that syntax is the col the field name, if not how do i specify which field i am doing the replacement on?

Lisa Romano

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 10:12:45

Do you have more examples?

Please post like this

DECLARE @Sample TABLE (OriginalData CHAR(7), WantedData MONEY)

INSERT @Sample
SELECT '000001A', 0.91 UNION ALL
SELECT '000001B', y.xx UNION ALL
SELECT '000022I', y.xx UNION ALL
SELECT '000093{', y.xx

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

lromano1962
Starting Member

13 Posts

Posted - 2007-10-24 : 10:13:47

Thanks... I will give it a try...

Lisa Romano

lromano1962
Starting Member

13 Posts

Posted - 2007-10-24 : 16:58:45

Here was the final outcome.

SELECT BilledTotalCharge, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BilledTotalCharge, '{', '0'), 'A',
'1'), 'B', '2'), 'C', '3'), 'D', '4'), 'E', '5'), 'F', '6'), 'G', '7'), 'H', '8'), 'I', '9') AS BilledCharge, Agency, Activity, BilledPhone, CallDate, ConnectTime, BillDate,
CellCallCharge, CellCallDuration, CAST(CellCallDuration AS float) / 10 AS CallDuration, CellCallConnectCity, CellCallConnectState,
CellCallConnectPhone
FROM dbo.CellCallInfo

Lisa Romano

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources