Hi guys, i got it. used charindex function in substring function

thankyou all

Thankyou very much, not sure why the other one did not worked though i have sqlserver 2008....

I tried adding as after values but it did not worked can anybody help how the error solved plss

HI Eugene Elutin,

Thanks for the reply, but its getting an error saying "incorrect syntax near keyword 'values'"

Thank you for the reply,

the Sales_yr is a Year column it has values like 2009, 2010, 2011 etc,...

Thanks Wayne its working gud

Thanks Falgunoza,

but the table has hierarchy till E like 2134E and may be the table gets updated timely

do this code works?

can you please explain the Waynes CTE code

Thanks

Hi Wayne thanks a lot the code is working gud

but i am really new to CTE`s can you please explain the code wat is doing

Thankyou

hi Falgunoza

all these are the sample data i have like 300 rows like this mixed cases 🙁

declare @test-2 table (RowID int IDENTITY, [new proj id] char(5), [old proj id] char(5))

insert into @test-2

SELECT '1150b', '1150/' UNION ALL

SELECT '1150c', '1150a' UNION ALL

SELECT '1150d', '1150b'...

thanks wayne the code is pretty much worked but i found a small problem

here is the source with row (New_proj_Id char(5), Old_proj_Id char(5), row_id)

SCAS1156C ,SCAS1156/ ...

Thanks Wayne, as i cant access the server after 5, so thought of reposting the question clearly with other cases and so did it,

sorry for that

Hi Jeff its my mistake, in the previous post initially i hav posted with only one business case but later when i got deep into the case, got to know...

the table have new project ids and old project id as in like proj id 1A is transfered to proj id 1b also the proj id 1b is transferd to...

using if else or while in cte we can achive this target i think need some help on this pls

new proj id old proj id

1084d 1084/

1084e 1084a

1084f 1084b

1077a 1077/

1077b 1077a

2134a...