Find first character in string if 0 only

Question

Find first character in string if 0 only

sql_Reporter

SSCrazy

Points: 2329
More actions
December 4, 2007 at 10:17 am

#153997

Hey can i write an express in SSIS Derived column, To replace the column if it has a 0 as the first char in the string An example of what i want to do is
014789632 would become 14789632
04567899 would become 04567899
222147896 would become 22247896
96325877 would be the same 96325877

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Can you explain why the second value should not be changed? Also, why are you replacing the first 2 characters from the first value?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Norman Kelm SSCommitted Points: 1922 More actions · Answer 2

RIGHT(Column,LEN(Column)-1)

Norman

DTS Package Search

http://www.dtspackagesearch.com/

John Rowan SSC Guru Points: 56440 More actions · Answer 3

That's not going to get him what he's described and shown in his example. Your code will always remove the first character and it sounds like he needs some conditional logic built into this.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Norman Kelm SSCommitted Points: 1922 More actions · Answer 4

That's true, John. Need to read more closely.

It's something like this then:

FINDSTRING(Column,"0",1) == 1 ? RIGHT(Column,LEN(Column)-1) : Column

It's not tested, but that should get you going.

Norman

DTS Package Search

http://www.dtspackagesearch.com/

sql_Reporter SSCrazy Points: 2329 More actions · Answer 5

Sorry guys that was a typo on my behalf, i only want to remove the first character if its a 0.

0123 will become 123

2349 will stay the same

I'll test you expression tomorrow. i do want to try and keep it in the dirived column as i like to replace the existing cloumn with the new output.

thanks

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

Cast to INT will get rid of leading zeros.

--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_Reporter SSCrazy Points: 2329 More actions · Answer 7

Thanks for the expression below, i've just one question regards it, i know both the findstring and right, the ? is use for the logic but am not sure why you've placed : column at the end of the experssion

FINDSTRING(Column,"0",1) == 1 ? RIGHT(Column,LEN(Column)-1) : Column

I think i understand what it means now, if my find string is turn do the Right else do nothing and just display the column as it is its expression conditionally evaluates

Norman Kelm SSCommitted Points: 1922 More actions · Answer 8

Yes, it's the same as

if(FINDSTRING(Column,"0",1) == 1)

return RIGHT(Column,LEN(Column)-1);

else

return Column;

The ? : snytax is from the C language and maybe even something before that I am not aware of. C is the first time I hit it after making the jump from FORTRAN 66. I'm old but not that old, just worked on some REALLY old computers at the beginning of my career.

Norman

DTS Package Search

http://www.dtspackagesearch.com/

sql_Reporter SSCrazy Points: 2329 More actions · Answer 9

Thanks for your help nite_eagle.

I do have another question you might be able to help me with. Am after making this change to my SSIS package, and have saved it. Do i now need to rebuild the package and Deploy it again. The package is already deployed.

Norman Kelm SSCommitted Points: 1922 More actions · Answer 10

It depends on how it is deployed.

Using the Integration Services service? Yes Import it into the package store again. The SQL Agent job will use the new package on the next job run.

That's the method I use. Stored in msdb via the Integrations Services service.

I can't answer with confidence on other methods. It does makes sense that the .dtsx file would need to be copied to the production server or wherever you are running the package as a job.

Norman

DTS Package Search

http://www.dtspackagesearch.com/