Remove Zero
-
Hi there,
How do I remove the zero/zero's before the first number? Like this
0002L - 2L
0011A - 11A
0027 - 27
0010A - 10A
0001AA - 1AA
0121 - 121
Thanks in advance
-
I'm surprised nobody answered this one faster :
--I use this table for many other string operations as well
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @i as int
set @i = 0
while @i < 8000
begin
Insert into dbo.Numbers Default values
set @i = @i + 1
end
GO
Declare @a table (Name varchar(15) primary key)
Insert into @a (Name) values ('0002L')
Insert into @a (Name) values ('0011A')
Insert into @a (Name) values ('0027')
Insert into @a (Name) values ('00100A')
Insert into @a (Name) values ('00001AA')
Insert into @a (Name) values ('0121')
Insert into @a (Name) values ('012100')
Insert into @a (Name) values ('A12100')
Select CASE WHEN dtA.Name is null THEN A.Name ELSE SUBSTRING(A.Name, dtA.StartPos, ABS(LEN(A.Name) - dtA.StartPos) + 1) END as Name from @a A LEFT OUTER JOIN
(
Select A2.Name, MAX(N.PkNumber) + 1 as StartPos from @a A2 inner join dbo.Numbers N on N.PkNumber <= LEN(A2.Name) where name like REPLICATE('[0]', PkNumber) + '%' GROUP BY A2.Name
) dtA
ON A.Name = dtA.Name
Order by Name
100A
11A
121
12100
1AA
27
2L
A12100
-
Thanks Remi!
I have been working with your reply for some time now.
But I need a littel bit elaboration on it.
First I have a tabel (A) with some 9000 rec. One column (Ab) is holding data in which I want to get rid of the zero/zero's before the first number (5700 different combinations). Now I have created a new column in this tabel (A) holding a Primary Key (based on identity clustered ect.).
Can it be done without declaring 5700 different variables?
Regards joejoe
-
Declare @a table (id int identity(1,1) primary key, Name varchar(15), NewCol varchar(15))
Insert into @a (Name) values ('0002L')
Insert into @a (Name) values ('0011A')
Insert into @a (Name) values ('0027')
Insert into @a (Name) values ('00100A')
Insert into @a (Name) values ('00001AA')
Insert into @a (Name) values ('0121')
Insert into @a (Name) values ('012100')
Insert into @a (Name) values ('A12100')
UPDATE A SET NewCol = CASE WHEN dtA.id is null THEN A.Name ELSE SUBSTRING(A.Name, dtA.StartPos, ABS(LEN(A.Name) - dtA.StartPos) + 1) END from @a A LEFT OUTER JOIN
(
Select A2.id, MAX(N.PkNumber) + 1 as StartPos from @a A2 inner join dbo.Numbers N on N.PkNumber <= LEN(A2.Name) where name like REPLICATE('[0]', PkNumber) + '%' GROUP BY A2.id
) dtA
ON A.id = dtA.id
Select * from @a
-
Once again thanks Remi
The 12 different examples of values with zero/zero's in the beginning is only a small part of the possible combinations 5724 all in all.
Maybe I'm reading your code incorrect, but as I understand it I will have to declare all the possible combinations?
Joe joe
-
The insert part is just for sample data to test the code.
-
Ok, I give up. I have been trying to rewrite your code but I can't figure it out.
How should this part look like if my tabel is called W and the Column with zeros to be removed called GT and the column with the Pk called PKNumber
UPDATE A SET NewCol = CASE WHEN dtA.id is null THEN A.Name ELSE SUBSTRING(A.Name, dtA.StartPos, ABS(LEN(A.Name) - dtA.StartPos) + 1) END from @a A LEFT OUTER JOIN
(
Select A2.id, MAX(N.PkNumber) + 1 as StartPos from @a A2 inner join dbo.Numbers N on N.PkNumber <= LEN(A2.Name) where name like REPLICATE('[0]', PkNumber) + '%' GROUP BY A2.id
) dtA
ON A.id = dtA.id
Select * from @a
I really appriciate your help!
-
Please post the actual table definition (create script). I'm not doing this 2 more times.
-
Of course,
CREATE TABLE [W] (
[GT] [char] (10) COLLATE Danish_Norwegian_CI_AS NULL ,
[PKNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_W] PRIMARY KEY CLUSTERED ([PKNumber])
-
The numbers table is used as a sequence table. It should not be merged with your actual data.
-
You could try this
declare @STR varchar(32)
select @STR = '0010A'
select reverse(replace(rtrim(replace(reverse(@str),0,' ')),' ',0))
it basically reverses the string, replaces 0's with spaces, trims off the trailing spaces, replaces any non trailing spaces with 0's and reverses it back
Note: You will need varchars for this to work properly
-
Finally a simple way
.Tx.
-
or
SELECT SUBSTRING([name],PATINDEX('%[^0]%',[Name]),LEN([Name])) FROM @a
Far away is close at hand in the images of elsewhere.
Anon. -
You seem to be the expert on paterns. Do you have a tutorial or a list of exemples of real life pattern searches (bol doesn't offer much in real life cases).
-
Thank you David!
This works very fine
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply