please help the query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

please help the query

Author

Topic

kabon
Starting Member

48 Posts

Posted - 2013-06-26 : 00:14:02

i have data like this :

OLD_LIMIT_ID
12243709.0008210.01::12243709.0008210.02::12243709.0008210.04

and i want to replace with the new ID:
12877614.0008200.01 = 12877614.0008200.07
12877614.0008200.03 = 12877614.0008200.08

and the result is :
NEW_LIMIT_ID
12243709.0008210.07::12243709.0008210.08::12243709.0008210.04

I have query like this but it doesn't work because it just add into the data just like this :
NEW_LIMIT_ID
12877614.0008210.01::12877614.0008210.02::12877614.0008210.03::12877614.0008210.08

here is the query :
SELECT A.[Column 3] AS OLD_LIMIT_ID,A.[Column 3]+'::'+LEFT(A.[Column 3],CHARINDEX('.',A.[Column 3],16))+RIGHT(NEW_ID_AB,2) AS NEW_LIMIT_ID FROM COLRTXT_20130624 A
INNER JOIN RESEQUENCE_LIMIT B ON LEFT(B.ID_AB,CHARINDEX('.',B.ID_AB)-1)=LEFT(A.[Column 3],CHARINDEX('.',A.[Column 3])-1)
and RIGHT(B.ID_AB,2)=RIGHT(A.[Column 3],2)

please help for solution

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-26 : 00:30:58

quote:


i have data like this :

OLD_LIMIT_ID [1]
12243709.0008210.01::12243709.0008210.02::12243709.0008210.04

and i want to replace with the new ID: [2]
12877614.0008200.01 = 12877614.0008200.07
12877614.0008200.03 = 12877614.0008200.08

and the result is :
NEW_LIMIT_ID [3]
12243709.0008210.07::12243709.0008210.08::12243709.0008210.04

Is the above the correct data ? You want to replace what with what ? i can't see the relationship between the [2] in the [3]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 02:27:57

Do you've a table where you store oldids against new ones? if yes, it should be a simple replace that you may require after doing a join with it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources