November 18, 2009 at 9:54 am
Hi
I would like some help with the following query.
The sql is designed to subtract 1 from the aplhanumeric value in the column ID and show the result in column [PREV_ID]
The combination of numeric and non-numeric characters can vary.
For example the prefix can vary like:
BD001
or
1234/01c
The last numeric digit is the one that gets incremented and in the case of the query below should be subtracted by 1.
The sql works fine until the number after the prefix is equal to 0 or 00
in these cases there is no previous id so the ID should just be copied over or made null.
Currently if ID = 1_0 the result is 0_0, it would be good if it was just kept as 1_0.
The sql also doesn't work where the number after the prefix is a multiple of 10, the sql seems to ignore the last numeric digit if it is 0, for example:
if ID = '4235_20d' the query currently produces the prev ID as '4235_10d' when it should be '4235_10d'.
Can the current sql be modified to fix the problems.
Thank you
DECLARE @Temp TABLE (ID VARCHAR(50),Type VARCHAR(50))
INSERT INTO @Temp VALUES ('BD001','BD')
INSERT INTO @Temp VALUES ('BD002','BD')
INSERT INTO @Temp VALUES ('BD003','BD')
INSERT INTO @Temp VALUES ('BD004','BD')
INSERT INTO @Temp VALUES ('BD005','BD')
INSERT INTO @Temp VALUES ('1234/01c','c')
INSERT INTO @Temp VALUES ('1234/02c','c')
INSERT INTO @Temp VALUES ('1234/03c','c')
INSERT INTO @Temp VALUES ('1234/04c','c')
INSERT INTO @Temp VALUES ('4235_01d','d')
INSERT INTO @Temp VALUES ('4235_02d','d')
INSERT INTO @Temp VALUES ('4235_03d','d')
INSERT INTO @Temp VALUES ('4231_0','')
INSERT INTO @Temp VALUES ('1_0','')
INSERT INTO @Temp VALUES ('100_01','')
INSERT INTO @Temp VALUES ('4235_20d','d')
INSERT INTO @Temp VALUES ('123456','d')
select ID
,case
when patindex('%[1-9]%', reverse(CS.ID))>0
then
reverse(
stuff(
reverse(
CS.ID)
, patindex('%[1-9]%', reverse(CS.ID)
)
,1
, cast(
substring(
reverse(
CS.ID
)
,patindex(
'%[1-9]%', reverse(CS.ID)
)
, 1)-1 as varchar
)
) )
else
null
end AS [PREV_ID]
from
@Temp CS
November 19, 2009 at 1:47 am
mistake in message, should be -
"if ID = '4235_20d' the query currently produces the prev ID as '4235_10d' when it should be '4235_19d'."
November 19, 2009 at 5:34 am
OK, here's an attempt:
DECLARE @Temp TABLE (ID VARCHAR(50),Type VARCHAR(50))
INSERT INTO @Temp VALUES ('BD001','BD')
INSERT INTO @Temp VALUES ('BD002','BD')
INSERT INTO @Temp VALUES ('BD003','BD')
INSERT INTO @Temp VALUES ('BD004','BD')
INSERT INTO @Temp VALUES ('BD005','BD')
INSERT INTO @Temp VALUES ('1234/01c','c')
INSERT INTO @Temp VALUES ('1234/02c','c')
INSERT INTO @Temp VALUES ('1234/03c','c')
INSERT INTO @Temp VALUES ('1234/04c','c')
INSERT INTO @Temp VALUES ('4235_01d','d')
INSERT INTO @Temp VALUES ('4235_02d','d')
INSERT INTO @Temp VALUES ('4235_03d','d')
INSERT INTO @Temp VALUES ('4231_0','')
INSERT INTO @Temp VALUES ('1_0','')
INSERT INTO @Temp VALUES ('100_01','')
INSERT INTO @Temp VALUES ('4235_20d','d')
INSERT INTO @Temp VALUES ('123456','d')
INSERT INTO @Temp VALUES ('abc', '')
INSERT INTO @Temp VALUES ('', '')
INSERT INTO @Temp VALUES (NULL, '')
;with Step1(ID, Type, ReversedID) as
(
select
ID, Type, reverse(ID)
from
@Temp
),
Step2(ID, Type, ReversedID, StartReversedLastNum) as
(
select
ID, Type, ReversedID, patindex('%[0-9]%', ReversedID)
from
Step1
),
Step3(ID, Type, StartReversedLastNum, ReversedTail) as
(
select
ID, Type, StartReversedLastNum,
isnull(substring(ReversedID, StartReversedLastNum, len(ReversedID)), '')
from
Step2
),
Step4(ID, Type, StartReversedLastNum, LenLastNum) as
(
select
ID, Type, StartReversedLastNum,
-- append a non-digit to avoid a zero return value.
patindex('%[^0-9]%', ReversedTail + 'x') - 1
from
Step3
),
Step5(ID, Type, LenLastNum, StartLastNum) as
(
select
ID, Type, LenLastNum,
len(ID) + 2 - StartReversedLastNum - LenLastNum
from
Step4
),
Step6(ID, Type, LenLastNum, StartLastNum, LastNum) as
(
select
ID, Type, LenLastNum, StartLastNum,
cast(substring(ID, StartLastNum, LenLastNum) as int)
from
Step5
),
Step7(ID, Type, NewID) as
(
select
ID, Type,
case
when LastNum > 0 then
stuff(ID, StartLastNum, LenLastNum,
right(replicate('0', LenLastNum) + cast(LastNum - 1 as varchar(10)), LenLastNum))
else
ID
end
from
Step6
)
select * from Step7
To get rid of the common subexpressions I splitted the problem up into several steps using common table expressions. It's a rather long story but it makes testing far more easy. Notice that the query optimizer combines all subexpressions within each CTE into a single expression to produce the end result (take a look at the Compute Scalar node in the execution plan):
[Expr1010] = Scalar Operator(CASE WHEN CONVERT(int,substring([ID],((len([ID])+(2))-patindex('%[0-9]%',reverse([ID])))-(patindex('%[^0-9]%',isnull(substring(reverse([ID]),patindex('%[0-9]%',reverse([ID])),len(reverse([ID]))),'')+'x')-(1)),patindex('%[^0-9]%',isnull(substring(reverse([ID]),patindex('%[0-9]%',reverse([ID])),len(reverse([ID]))),'')+'x')-(1)),0)>(0) THEN stuff([ID],((len([ID])+(2))-patindex('%[0-9]%',reverse([ID])))-(patindex('%[^0-9]%',isnull(substring(reverse([ID]),patindex('%[0-9]%',reverse([ID])),len(reverse([ID]))),'')+'x')-(1)),patindex('%[^0-9]%',isnull(substring(reverse([ID]),patindex('%[0-9]%',reverse([ID])),len(reverse([ID]))),'')+'x')-(1),right(replicate('0',patindex('%[^0-9]%',isnull(substring(reverse([ID]),patindex('%[0-9]%',reverse([ID])),len(reverse([ID]))),'')+'x')-(1))+CONVERT(varchar(10),CONVERT(int,substring([ID],((len([ID])+(2))-patindex('%[0-9]%',reverse([ID])))-(patindex('%[^0-9]%',isnull(substring(reverse([ID]),patindex('%[0-9]%',reverse([ID])),len(reverse([ID]))),'')+'x')-(1)),patindex('%[^0-9]%',isnull(substring(reverse([ID]),patindex('%[0-9]%',reverse([ID])),len(reverse([ID]))),'')+'x')-(1)),0)-(1),0),patindex('%[^0-9]%',isnull(substring(reverse([ID]),patindex('%[0-9]%',reverse([ID])),len(reverse([ID]))),'')+'x')-(1))) ELSE [ID] END)
I didn't even try to read it nor to reverse engineer it back into one T-SQL expression :-). That's what optimizers are for.
Also notice that I added three testcases. All three produce ID as NewID. It's up to you if this is correct.
HTH,
Peter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply