Remove first character and add trailing zero

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

Remove first character and add trailing zero

Author

Topic

HelalM
Starting Member

19 Posts

Posted - 2012-10-17 : 19:22:52

My data in column A look like:
-0002
00030
-0004
00050

I need to remove the first character '-' when occurs and then add a trailing zero so result is:
00020
00030
00040
00050

Thank You for your help

HM

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-10-17 : 19:33:36

[CODE]select
case when left(ColA, 1) = '-' then right(ColA, len(ColA) - 1) + '0'
else ColA
end ColA
from
MyTable[/CODE]

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-17 : 23:09:18

SELECT LEFT(REPLACE(field,'-','')+'0',5) FROM table

if there are no other - characters

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

HelalM
Starting Member

19 Posts

Posted - 2012-10-18 : 13:32:43

Awesome...thank you

HM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-18 : 14:07:27

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources