help with sql 2000
-
can some one help me i hate a table that has a matched pair number
ie..
icqrm006520050406142936mxfs100100985-ddpgw000020041209004511mxfs100088798
icqrm006520050406142936mxfs100100995-ddpgw000020041209004512mxfs100088328
icqrm006520050406142936mxfs100100995-icqrm006520050406142936mxfs100100995
and im trying to make a new table that has a procedure to go throught that original table and search if the left side of the dash is = to the right side of the dash and if it is exclude that from the info sent into the new table i want to create...if anyone can help it would be appreciated
-
charindex can help you find the positions of the dash and the =. substring can then extract that part out and you can use that for the comparison.
Look them up in BOL and practice.
-
--Create temp table to test out the query
Create table temp (pk int identity, Test1 varchar(100))
-- Insert into the Temp Table
insert into temp (test1)
Select 'icqrm006520050406142936mxfs100100985-ddpgw000020041209004511mxfs100088798'
Union All
Select 'icqrm006520050406142936mxfs100100995-ddpgw000020041209004512mxfs100088328'
Union All
Select 'icqrm006520050406142936mxfs100100995-icqrm006520050406142936mxfs100100995'
-- Run the Test query
select *
from Temp
where substring(Test1,1,charindex('-', Test1)-1) <> substring(Test1,charindex('-', Test1) +1, 100)
-- Results
--pk,Test1
1,icqrm006520050406142936mxfs100100985-ddpgw000020041209004511mxfs100088798
2,icqrm006520050406142936mxfs100100995-ddpgw000020041209004512mxfs100088328
drop table Temp
-
Ray M the only problem i have is that i have a huge db and i dont' want to have to type in the pairs....
-
This is only for a demo... just use this code and use the right table/column names :
select *
from dbo.Temp
where substring(Test1,1,charindex('-', Test1)-1) substring(Test1,charindex('-', Test1) +1, 100)
-
thanks that worked
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply