how to replace last character of 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

General SQL Server Forums

New to SQL Server Programming

how to replace last character of string

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-05 : 16:50:33

how do i replace the last character ;

declare @tbl1 table
(ID INT,
Email varchar(255)
)

INSERT INTO @tbl1
SELECT 1, 'Aa Bb <abb@xyz.com>; Pr, Eda <peda@abc.com>;'

INSERT INTO @tbl1
SELECT 2, 'Ra, Ja <rja@cc.com>; Rr Gg <rgg@cc.com>;'

INSERT INTO @tbl1
SELECT 3, 'Ni Mnn <nimnn@mmm.com>; Myjob <myjob@mycompany.com>;'

the output looks like this:
Id Email
1 Aa Bb <abb@xyz.com>; Pr, Eda <peda@abc.com>
2 Ra, Ja <rja@cc.com>; Rr Gg <rgg@cc.com>
3 Ni Mnn <nimnn@mmm.com>; Myjob <myjob@mycompany.com>

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-03-05 : 17:07:51

you use the replace function? replace(Email, '>;', '>|') this replaces your last ; to |

and this table is a bit denormalized, don't you think so?

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-05 : 23:31:07

Try this once,

declare @tbl1 table
(ID INT,
Email varchar(255)
)

INSERT INTO @tbl1
SELECT 1, 'Aa Bb <abb@xyz.com>; Pr, Eda <peda@abc.com>;'

INSERT INTO @tbl1
SELECT 2, 'Ra, Ja <rja@cc.com>; Rr Gg <rgg@cc.com>;'

INSERT INTO @tbl1
SELECT 3, 'Ni Mnn <nimnn@mmm.com>; Myjob <myjob@mycompany.com>;'

select id,reverse(substring(reverse(email),2,len(email))) email from @tbl1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-05 : 23:46:57

try this one too
select id,substring(email,1,len(email)-1)as email from @tbl1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources