March 29, 2012 at 1:16 pm
--basically just need a quick way to remove leading zero's
declare @string varchar(7)
set @string = '000123'
select '123' [should equal],@string actual
set @string = '00123'
select '123' [should equal],@string actual
set @string = '0123'
select '123' [should equal],@string actual
set @string = '123'
select '123' [should equal],@string actual
set @string = '01020z'
select '1020z' [should equal],@string actual
March 29, 2012 at 1:22 pm
DECLARE @string VARCHAR(7) = '000123';
WHILE SUBSTRING(@string, 1, 1) = '0'
SET @string = STUFF(@string, 1, 1, '');
SELECT @string;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 29, 2012 at 1:25 pm
This?
SELECT Tab.String
,NewStr = CASE WHEN LEFT(Tab.String,1) = '0' THEN STUFF (Tab.String,1,1,'')
ELSE Tab.String
END
FROM
( SELECT '000123'
UNION ALL SELECT '00123'
UNION ALL SELECT '0123'
UNION ALL SELECT '123'
UNION ALL SELECT '01020z'
) Tab ( String )
March 29, 2012 at 1:26 pm
THis?
SELECT Tab.String
,NewStr = CASE WHEN LEFT(Tab.String,1) = '0' THEN STUFF (Tab.String,1, PATINDEX('%[1-9]%',Tab.String) -1 , '' )
ELSE Tab.String
END
FROM
( SELECT '000123'
UNION ALL SELECT '00123'
UNION ALL SELECT '0123'
UNION ALL SELECT '123'
UNION ALL SELECT '01020z'
) Tab ( String )
March 29, 2012 at 2:16 pm
ColdCoffee (3/29/2012)
THis?
SELECT Tab.String,NewStr = CASE WHEN LEFT(Tab.String,1) = '0' THEN STUFF (Tab.String,1, 1 , '' )
ELSE Tab.String
END
FROM
( SELECT '000123'
UNION ALL SELECT '00123'
UNION ALL SELECT '0123'
UNION ALL SELECT '123'
UNION ALL SELECT '01020z'
) Tab ( String )
That gets rid of one leading zero, but not all of them.
Edit: Never mind. Didn't notice you changed it in the second version.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 29, 2012 at 2:17 pm
You don't actually need the case statement:
SELECT STUFF(Y, 1, PATINDEX('%[^0]%', Y)-1, '')
FROM (VALUES('123'),('0123'),('000123')) AS X(Y);
Works just fine.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 29, 2012 at 3:10 pm
GSquared (3/29/2012)
You don't actually need the case statement:
SELECT STUFF(Y, 1, PATINDEX('%[^0]%', Y)-1, '')FROM (VALUES('123'),('0123'),('000123')) AS X(Y);
Works just fine.
Yeah Gus, it occured to me just after posting the code. Thanks for jumping in.
March 29, 2012 at 3:10 pm
If it's something you are going to use quite a bit, you might want to create a function.
Create FUNCTION [dbo].[DropLeadingZeros](
@iString VARCHAR(100)
) RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @oStringvarchar(100)
Select @oString = Substring(@iString,patindex('%[^0]%',@iString),100)
RETURN (@oString)
END
---------------------------------------------------
Declare @LeadingZeros Table
(
sValuevarchar(100)
)
Insert into @LeadingZeros
SELECT '000123'
UNION ALL
SELECT '00123'
UNION ALL
SELECT '0123'
UNION ALL
SELECT '123'
UNION ALL
SELECT '01020z'
Select sValue as OrigValue,[dbo].[DropLeadingZeros](sValue) as NewValue
From @LeadingZeros
March 29, 2012 at 3:32 pm
You can also just do it inline like this:
Declare @LeadingZeros Table
(
sValuevarchar(100)
)
Insert into @LeadingZeros
SELECT '000123'
UNION ALL
SELECT '00123'
UNION ALL
SELECT '0123'
UNION ALL
SELECT '123'
UNION ALL
SELECT '01020z'
UNION ALL
SELECT '0s.123'
Select sValue as OrigValue,Substring(sValue,patindex('%[^0]%',sValue),len(sValue)) as NewValue
From @LeadingZeros
March 29, 2012 at 7:25 pm
STUFF with PATINDEX is cool! This will also work:
SELECT REPLACE(LTRIM(REPLACE(Y, '0', '#')),'#', '0')
FROM (SELECT '123' UNION ALL SELECT '0123'
UNION ALL SELECT '000123' UNION ALL SELECT '0001230') AS X(Y);
Assuming of course that there are no # in your string.
I wanted to use VALUES to compress my solution but my SQL Server 2005 would have no part of it.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 30, 2012 at 8:43 am
...and just to show how ill-suited sql can sometimes be for string manipulation here is a C# snippet with a purpose built function that can be used to do the same thing:
string strWtihLeadingZeros = "000TestString";
strWithLeadingZeros.TrimStart("0".ToCharArray());
It would be interesting to see if this stuck in a CLR Object would be quicker than a Sql Solution.
March 30, 2012 at 9:17 am
interesting stuff. Thanks guys.....
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply