Replace just the last charatcter

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)

Replace just the last charatcter

Author

Topic

ssunny
Posting Yak Master

133 Posts

Posted - 2013-02-13 : 11:10:29

Hello All,

I know this has been asked for million times already but I can't seem to get it right.
I need to replace just the last character from a string.

I have a update_date column in my table and datatype is datetime(). But I need to convert it to varchar and replace last character with 'Z' for the work I am doing. One thing I know is length of update_date column is always 19

input string: 2012-11-19 13:53:41.377
output I want : 2012-11-19 13:53:41.37Z

I have tried using left,right,charindex,substring functions without any luck.

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-13 : 11:21:50

If the update_date column is of data type DATETIME, you cannot store the value with the "Z" in it. DATETIME stores the data in an internal format (a 2-byte representation of the date+time), not as a character type column. You can of course, convert the data to a string on the fly and retrieve it - for example like this:

STUFF(CONVERT(VARCHAR(32),update_date,121),23,1,'Z')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 11:27:55

stuff(convert(varchar(30),update_date,121),len(convert(varchar(30),update_date,121)),1,'Z')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-13 : 11:35:29

If you always have milliseconds then

convert(varchar(22),update_date,121) + 'Z'

djj

ssunny
Posting Yak Master

133 Posts

Posted - 2013-02-13 : 11:56:11

Wow thank you James,Visakh and djj55 but I have a new problem now.I should have mentioned the whole thing in the beginning itself. Sorry about that.

original input string : 2012-11-19 13:53:41.377
original output wanted: 2012-11-19T13:53:41.37Z

I was doing replace ' ' with 'T' for inserting 'T' before, but now I don't how to get output in single statement merging REPLACE for 'T' and STUFF for 'Z'

ssunny
Posting Yak Master

133 Posts

Posted - 2013-02-13 : 12:26:25

Nevermind, I got it. Thank you all.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-02-13 : 13:12:18

Please post your solution so if someone has a similar problem they know what you did.

djj

ssunny
Posting Yak Master

133 Posts

Posted - 2013-02-13 : 13:33:06

Here's the working solution.

declare @update_date varchar (100)
set @update_date = '2012-11-19 13:53:41.377'
select stuff(REPLACE (CONVERT (varchar(100),@update_date,121) ,' ','T'),len(convert(varchar(100),@update_date,121)),1,'Z')

Thanks.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-02-13 : 19:24:28

[code]
DECLARE @update_date varchar (100)
SET @update_date = '2012-11-19 13:53:41.377'
SELECT REPLACE (CONVERT (varchar(22),@update_date,121) ,' ','T') + 'Z'
[/code]

ssunny
Posting Yak Master

133 Posts

Posted - 2013-02-14 : 12:10:22

Thank you ScottPletcher for even better solution. Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources