getting rid of 0's in the start of a varchar(6) column

Question

Post reply

getting rid of 0's in the start of a varchar(6) column

sql_learner29

SSCrazy Eights

Points: 9107
More actions
November 18, 2008 at 8:55 am

#124948

Hi All,
I am facing a problem in avoiding trailing zeros from my stage source…
Actually I have 3 columns which require to be looked up from an emp code from employee dimension. But the stage source that I am getting has codes starting with zeros…
Like 006708 (varchar(6)
But in emp dimension it is present as 6708 (varchar(6)
Some are like A09876 (so some sarts with char too…
Now I want to split them getting from the source and also want to get rid of zeros in the start ?
Any help on this?
Thanks in advance for your help.
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Raj's Ten Centuries Points: 1044 More actions · Answer 1

Hi,

You should be using derived column transformation, and use substring or available string functions. This would help.

Raj

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Rajesh (11/18/2008)
Hi,
You should be using derived column transformation, and use substring or available string functions. This would help.
Raj

Easy enough to say... what formula would you use to return the following in the format requested by the OP?

126708

A26708

AB6708

026708

006708

A06708

AB6708

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Raj's Ten Centuries Points: 1044 More actions · Answer 3

I am sorry, little confused with the reply. Could you be specific, what's the input and what needs to be the op with few examples. If the the volume of data is not large enough you could use cursors or case conditions in select statement.

Cheers,

Raj

Daveee SSCommitted Points: 1600 More actions · Answer 4

Hi,

How about a function that strips the Alpha out like this

CREATE FUNCTION dbo.RemoveAlpha(@Str varchar(1000))

RETURNS VARCHAR(1000)

BEGIN

declare @NewStr varchar(1000),

@i int

set @i = 1

set @NewStr = ''

while @i <= len(@str)

-- STRIP OUT ALPHA CHARS

begin

if substring(@str,@i,1) like '%[0-9]%'

begin

set @NewStr = @NewStr + substring(@str,@i,1)

end

else

begin

set @NewStr = @NewStr

end

set @i = @i + 1

end

RETURN Rtrim(Ltrim(@NewStr))

END

Then get the data using the function whilst weeding out the 0's

SELECT REPLACE(LTRIM(REPLACE(([dbo].[RemoveAlpha](SomeEmployeeNumColumn)) , '0', ' ')), ' ', '0')

FROM EmployeesTable

Test ok on my box as

126708

A26708

AB6708

026708

006708

A06708

AB6708

Becomes

126708

26708

6708

26708

6708

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 5

I don't want to remove characters from the start....

if it starts with a char leave it as it is....

Only if it starts with 0 trim the zeros

Thanks

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 6

Well the input is like this:

Test ok on my box as

006708

A26708

CB6708

026708

006708

K06708

AB6708

And the output should be like this:

6708

A26708

CB6708

26708

6708

K06708

AB6708

Hope it's clear?

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Rajesh (11/19/2008)
I am sorry, little confused with the reply. Could you be specific, what's the input and what needs to be the op with few examples. If the the volume of data is not large enough you could use cursors or case conditions in select statement.
Cheers,
Raj

I was suggesting that you actually offer a code solution to the original poster. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246962 More actions · Answer 8

If it's numeric, cast to integer, otherwise leave as is:

declare @value varchar(10)

Set @Value = '0000006708'

if IsNumeric(@value) = 1

select cast(@value as integer)

else

select @value

Phil

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

Phil Parkin (11/19/2008)
If it's numeric, cast to integer, otherwise leave as is:
declare @value varchar(10)
Set @Value = '0000006708'
if IsNumeric(@value) = 1
select cast(@value as integer)
else
select @value
Phil

Works fine... but OP also wants to know what to do with things like A06708.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 10

Thanks Phil..It works now

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

Ria (11/21/2008)
Thanks Phil..It works now

Umm... what about the things that start with "A"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)