removing last two char - 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

General SQL Server Forums

New to SQL Server Programming

removing last two char

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2007-04-25 : 12:12:25

Afternoon all

What is the quickest way to remove the last two char from a colunm and leaving the rest of the colunm intact.

cheers

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 12:18:04

[code]select col, left(col, datalength(col) - 2) from table[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-25 : 12:42:13

By 'remove' if you mean replace/update, then you can do a REPLACE on the column with a variation of Harsh's code.


UPDATE <Table>
SET col1 = right(col1, len(col1) - 2)
WHERE <condition>

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

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-25 : 12:43:04

Also, before you do the update, I would recommend yo do a SELECT with the same expression and see what you are updating with is same as what you are expecting to.

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

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-25 : 12:51:23

The query will fail if you have values in your column that has a length of less than 2 characters. To avoid this, here's an updated version of the query of harsh_athalye:

select col, CASE WHEN datalength(col) < 2 THEN col else left(col, datalength(col) - 2) END as col
from table

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources