Strip this!!!
-
Does anyone have a set based way to remove preceeding zeros from a varchar field? The number of zeros that preceed this data is widely variable too.
I have data that is like such...
000H.K6
004ghs
00000l.sa
lh008
A.J.
DBA with an attitude -
Forget it... I found it.
My buddy Bill here showed me the light..
select REPLACE(LTRIM(REPLACE(columX, '0', ' ')), '', '0') AS new_col1 .....
A.J.
DBA with an attitude -
Are you sure thats what you need? Wouldn't this remove all zeros and not just the preceeding ones?
DECLARE @Var table (col1 varchar(10))
INSERT INTO @var VALUES ( '000H.K6' ) INSERT INTO @var VALUES ( '004ghs' ) INSERT INTO @var VALUES ( '00000l.sa' ) INSERT INTO @var VALUES ( 'lh008' )
select REPLACE(LTRIM(REPLACE(col1, '0', ' ')), '', '0') AS new_col1 FROM @var
Results in,
H.K6 4ghs l.sa lh 8
--------------------
Colt 45 - the original point and click interface
-
yeah... actually I found that out already...
this is better
select SUBSTRING(column1, PATINDEX('%[^0]%', column1+'A'), LEN(column1))
A.J.
DBA with an attitude -
Hmm ... I recognise that solution ...
--------------------
Colt 45 - the original point and click interface -
Yup I stole it..
A.J.
DBA with an attitude -
select REPLACE(LTRIM(REPLACE(col1, '0', ' ')), ' ', '0') AS new_col1 FROM @var
The second replace should be an space instead of an empty string for it to work!
in any case I would also use a where clause for performance
where col1 like '0%'
* Noel -
Problem is... that replaces all spaces. Which is no good for what I am doing.
A.J.
DBA with an attitude -
Well if spaces are important to you that's a different thing (you din't said that
)definitely the patindex method is way to go
... Still use the where clause though Cheers,
* Noel -
Why not just create a fuction?
Supply your string as a parameter, create WHILE LEFT(@InputString, 1) = '0'...
inside of the function and use result of the fuction in your select.
P.S. I wrote similar prescription in another topic several hours ago.
_____________
Code for TallyGenerator -
because I want it to actually run fast.
A.J.
DBA with an attitude -
Why create a function for something that can be achieved in on T-SQL statement?
--------------------
Colt 45 - the original point and click interface -
Amen brother Phil!!!!
A.J.
DBA with an attitude -
It will be fast.
There are no disk operations inside of this function, so you probably would not notice the difference.
_____________
Code for TallyGenerator -
Functions like that will be run for each and every row in that table I am querying though vs. once in a set based operation.
A.J.
DBA with an attitude
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply