Stripping leading zeros from mixed data 6

[jfokas]

I am trying to manipulate data in a column that has both numberic and string values. The column has a datatype of varchar. I need to strip any numeric values of leading zeros. So that "00045300" will appear as "45300".

In the past, I've simply converted the data to an int datatype and that took care of the problem, but in this case I cannot because some rows are straight text and some are mixed, e.g "000254T".

Does anyone have a suggestion as to how I can achieve my goal?

 

[RiverGuy]

You could easily do this in VB. I'm positive you could make a stored procedure in SQL Server as well, but I'm not versed enough in the syntax to be able to tell you how.

That said, if you wanted to try it in VB, I did something similar of doing string evaluations and removing single quotes. The only difference is that I was pasting new records into a database. Your problem would be no different because you could pull over the unique identifier and update the column when you find them with leading zero's. If you wanted to try this is VB or another language that you can compile an executable with, you can always put it inside a DTS package on SQL Server if you wanted to run it from Enterprise Manager.

 

[MeanGreen]

I know that this is not what you were looking for, but you could try the following code in your select statement and get the leading zeros removed:

SELECT COL1,
LTRIM(RTRIM(SUBSTRING(COL1,CASE WHEN LEFT(COL1,4) = '0000' THEN 5
WHEN LEFT(COL1,3) = '000' THEN 4
WHEN LEFT(COL1,2) = '00' THEN 3
WHEN LEFT(COL1,1) = '0' THEN 2
ELSE 1
END,DATALENGTH(COL1)))) FROM TEST2

where COL1 is a column with leading zeros in your TEST2 table. Of course, if you have more than 4 leading zeros then you would need to add that to the case statement. This is a quick and dirty way to display your data without leading zeros. You could set this up as a view on your table and then your users would never know that their were any leading zeros. You might also consider adding a trigger to the table to remove leading zeros on input and then you would not have the issue later. Hope this helps.

 

[rt63]

One very simple method (assuming no spaces in the column) is


select replace(ltrim(replace(<your column>,'0',' ')),' ','0')


If you have spaces as well in the column, then you can use some character (say ^ which is not expected in your column data and rewrite the select as


select replace(replace(ltrim(replace(replace(<your column>,' ','^'),'0',' ')),' ','0'),'^',' ')


This way you will not depend on how many leading zeros to cater for in your case statement.


RT

 

[tlbroadbent]

Excellent code, RT. Deserves a star! Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[rt63]

Thanks Craig/Terry !


RT

 

[jfokas]

RT, Thanks!! It works beautifully. I dialed in to my site and replaced a ugly CASE statement that I had written with your code.

I have one question for you. I think that I haven't had enough coffee this morning and I'm feeling dense, but when I first looked your statement I was convinced that it wouldn't work because it would replace all zeros in the field. Clearly, it doesn't but I still can't seem to grasp why not. Can you help clear the fog from my brain?

Again, I really do appreciate the input. It's helped me out greatly with this client and I am going to tuck this one away for future reference.

John

 

[tlbroadbent]

RT's code does change all zeros to spaces, trims leading spaces and then converts spaces to zeros. That is the reason for the second piece of code - in case the columns already contains embedded spaces.

Functions work from right to left or from the inside function to the outside function. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[jfokas]

Thanks, that makes perfect sense now. Definitely a trick that can be used in many situations. I appreciate the explaination.