SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 please help the query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kabon
Starting Member

Indonesia
48 Posts

Posted - 06/26/2013 :  00:14:02  Show Profile  Reply with Quote
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)

Singapore
17642 Posts

Posted - 06/26/2013 :  00:30:58  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/26/2013 :  02:27:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000