Eliminating Leading Char Zeros
-
Hello,
I have a 14 character string where the values start with zero, and can contain up to 13 zeros. The non zero values can be Alpha or Numeric. From this string, I need to extract the first occurence of a non zero value, and everything to the right of that value.
For example, consider the following strings;
00000000000002
00000000000100
00000000002100
00000000100020
0000000000A454
From those stings, I would need this;
2
100
2100
100020
A454
If you have any ideas on this, please share.
Thank you for your help!
CSDunn
-
--Create a tally table of sequential numbers
SELECT TOP 1000000
N=IDENTITY(INT,1,1)
INTO Tally
FROM Master.dbo.SysObjects sc1,
Master.dbo.SysObjects sc2
--Make sure it is indexed
ALTER TABLE Tally ADD PRIMARY KEY CLUSTERED (N)
--Create my test data
CREATE TABLE #test (testid INT IDENTITY(1,1), Value VARCHAR(14))
INSERT INTO #Test SELECT '00000000000002'
INSERT INTO #Test SELECT '00000000000100'
INSERT INTO #Test SELECT '00000000002100'
INSERT INTO #Test SELECT '00000000100020'
INSERT INTO #Test SELECT '0000000000A454'
--Select the results
SELECT t.TestID, SUBSTRING(Value,d.N,14), Value
FROM #test t INNER JOIN (
SELECT testID, MIN(ta.N) N
FROM #Test tt CROSS JOIN Tally ta
WHERE N<15
AND SUBSTRING(tt.Value,N,1)<>'0'
GROUP BY TestID) d
ON t.TestID=d.TestID
Brian
-
Alternate solution
DECLARE @test-2 TABLE (testid INT IDENTITY(1,1), Val VARCHAR(14))
INSERT INTO @test-2 SELECT '00000000000002'
INSERT INTO @test-2 SELECT '00000000000100'
INSERT INTO @test-2 SELECT '00000000002100'
INSERT INTO @test-2 SELECT '00000000100020'
INSERT INTO @test-2 SELECT '0000000000A454'
SELECT
CASE WHEN PATINDEX('%[A-Z]%',Val) = 0 THEN RIGHT(Val, LEN(Val) - PATINDEX('%[1-9]%' ,Val)+1) -- only numbers 1-9
WHEN PATINDEX('%[1-9]%',Val) = 0 THEN RIGHT(Val, LEN(Val) - PATINDEX('%[A-Z]%',Val)+1) -- only A-Z
ELSE -- Mix of both
CASE WHEN PATINDEX('%[A-Z]%',Val) < PATINDEX('%[1-9]%',Val) -- if A-Z comes first
THEN RIGHT(Val, LEN(Val) - PATINDEX('%[A-Z]%',Val)+1) -- 1-9 comes first
ELSE RIGHT(Val, LEN(Val) - PATINDEX('%[1-9]%' ,Val)+1)
END
END
FROM @test-2
WHERE PATINDEX('%[1-9]%',Val)> 0
OR PATINDEX('%[A-Z]%',Val)> 0
-
declare @Temp table (
Data nvarchar(14)
)
insert into @Temp
select '00000000000002'
insert into @Temp
select '00000000000100'
insert into @Temp
select '00000000002100'
insert into @Temp
select '000000000A4504'
select
case
when isnumeric(data) = 1 then cast(cast(data as bigint) as nvarchar(14))
else replace(ltrim(replace(data,'0',' ')),' ','0')
end
from @Temp
--
-
A word of caution about using ISNUMERIC. Check this post.
-
Thank you for the responses!
CSDunn
-
Why so complicated queries?
select
testid,
val,
substring(val, patindex('%[^0]%', val), 14) AS PesoChoice
from @test-2
N 56°04'39.16"
E 12°55'05.25" -
Nice
* Noel -
Because sometimes the simple answers are the hardest to see.
Brian
-
Perfect...
--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) -
-
Sergyi, you are a whiner.
I quote OP first post " From this string, I need to extract the first occurence of a non zero value, and everything to the right of that value.".
There are no non-zero characters within the 14 character column width, so there is nothing to extract. Even if you only put in a single zero there are still no non-zero charcters to return.
This leads to a special case which [in case of all characters are zero]
1) Keep all zeros (1-14)
2) Return an empty string
Until OP returns and tells us what to do with this special case, I will keep my simple formula because I am pretty darn sure that there is some kind of contraint that prohibits 13 zero characters from being stored.
But thanks for caring anyway, Sergiy!
N 56°04'39.16"
E 12°55'05.25" -
Sergyi, will you do me the favor of revising the algorithm I posted here
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=381713
It might have a bug I haven't spotted yet. If you find any, I would be most grateful to fix it.
Thanks.
N 56°04'39.16"
E 12°55'05.25" -
I did not say solution is bad.
I objected Jeff's statement that it's perfect.
_____________
Code for TallyGenerator -
Don't you just adore helper tables?
You can do this with a smaller table, though...
DROP TABLE #Zeros CREATE TABLE #Zeros (HowMany int, zerostring Varchar(13)) INSERT INTO #Zeros SELECT 1, '0' UNION ALL SELECT 2, '00' UNION ALL SELECT 3, '000' UNION ALL SELECT 4, '0000' UNION ALL SELECT 5, '00000' UNION ALL SELECT 6, '000000' UNION ALL SELECT 7, '0000000' UNION ALL SELECT 8, '00000000' UNION ALL SELECT 9, '000000000' UNION ALL SELECT 10, '0000000000' UNION ALL SELECT 11, '00000000000' UNION ALL SELECT 12, '000000000000' UNION ALL SELECT 13, '0000000000000'
DROP TABLE #Test CREATE TABLE #Test (TestID int IDENTITY(1,1), Value VARCHAR(14)) INSERT INTO #Test SELECT '00000000000002' INSERT INTO #Test SELECT '00000000000100' INSERT INTO #Test SELECT '00000000002100' INSERT INTO #Test SELECT '00000000100020' INSERT INTO #Test SELECT '0000000000A454'
SELECT t.TestID, STUFF(t.[Value], 1, MAX(z.HowMany), '') FROM #Test t INNER JOIN #Zeros z ON t.Value LIKE (z.zerostring + '%') GROUP BY t.TestID, t.[Value] ORDER BY t.TestID
Cheers
ChrisM
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply