Remove the 0 before - 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

Remove the 0 before

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2012-01-08 : 15:49:23

HI,
I need the query for below
I have data from flat file and i want to save the code column without 0 before

columns
code nvarchar(7)
name nvarchar(40)

----------
flat file
----------
EmpCode name
003 Raj
048 Kumar
073 selvam
100 gopal
140 gomathi
T3R Nayaki
YEU Yasodha
OPE Easwari
063 Rani

I want to insert removing 0 before code column
----------
SQL Insert
----------
EmpCode Name
3 Raj
48 Kumar
73 selvam
100 gopal
140 gomathi
T3R Nayaki
YEU Yasodha
OPE Easwari
63 Rani

Kindly help me with query
I tried below and not working :(
LTRIM(EmpCode,'0');
(CAST (CAST EmpCode AS int) AS varchar(7))

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-08 : 15:56:40

CASE WHEN ISNUMERIC(EmpCode) THEN CAST (CAST EmpCode AS int) AS varchar(7) ELSE EmpCode END

Not 100% guaranteed, but it should work.

p.s. LTRIM is only for removing leading spaces, and it doesn't take 2 parameters. See Books Online.

--
Gail Shaw
SQL Server MVP

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2012-01-08 : 16:16:36

Not working :(

quote:
Originally posted by GilaMonster

CASE WHEN ISNUMERIC(EmpCode) THEN CAST (CAST EmpCode AS int) AS varchar(7) ELSE EmpCode END

Not 100% guaranteed, but it should work.

p.s. LTRIM is only for removing leading spaces, and it doesn't take 2 parameters. See Books Online.

--
Gail Shaw
SQL Server MVP

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-08 : 16:52:49

And 'Not Working' means what? I can't see your screen, I don't have your data, I can't read your mind. If you want us to help you, you need to put in a little bit of effort.

--
Gail Shaw
SQL Server MVP

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2012-01-08 : 17:03:33

Ok.. consider the below scenario
Code
----
003
073
140
T3R

I want result of SQL as

Code
----
3
73
140
T3R

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-01-08 : 17:05:56

DECLARE @Table TABLE (EmpCode char(3),name varchar(20))
INSERT INTO @Table
SELECT '003','Raj' UNION
SELECT '048','Kumar' UNION
SELECT '073','selvam' UNION
SELECT '100','gopal' UNION
SELECT '140','gomathi' UNION
SELECT 'T3R','Nayaki' UNION
SELECT 'YEU','Yasodha' UNION
SELECT 'OPE','Easwari' UNION
SELECT '063','Rani'

select
stuff(empcode,1,
patindex('%[^0]%',empcode) -1,'')

,empcode from @table

Jim

Everyday I learn something that somebody else already knew

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2012-01-08 : 17:23:30

thanks jim , it works !

THANKS
SHANMUGARAJ
nshanmugaraj@gmail.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-01-09 : 01:41:50

or

select cast(empcode as int) from @Table
where empcode not like '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources