March 4, 2014 at 9:52 am
I think I need a Patindex or Like in my TSQL to update a table, but I need a little help. I want to update domtype with "Client" and shortn with the value in cdom WHERE the value in cdom exists in clientdomain. (case insensitive)
Table1
fields: domtype shortn clientdomain
Data:
NULL , "Loblaw" , "this is loblaw company"
NULL , NULL , "My Weston way"
NULL , NULL, "ACME suppliers"
NULL , NULL, "We are wesfair.ca partners"
Table2
fields: cdom
Data:
loblaw
weston.ca
wesfair.ca
acme
March 4, 2014 at 11:33 am
Yes, you need either PATINDEX or LIKE in order to do what you want. You'd need to test to find out which one will perform better.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2014 at 1:09 pm
Select CD.*
From ClientDomains as CD , DomainShortList as CD2
Where Substring(CD2.DomainName, Charindex(CD2.DomainName,CD.DomainName),100) IN (Select DomainName From DomainshortList )
This is not working for me... Can someone help me with my code? I am trying it with a select statement first.
March 4, 2014 at 1:40 pm
I think one of these should work for you:
DECLARE @clientDomains TABLE
(
DomainName VARCHAR(1000)
);
DECLARE @DomainShortList TABLE
(
DomainName VARCHAR(100)
);
INSERT INTO @clientDomains
VALUES ( 'this is loblaw company' );
INSERT INTO @DomainShortList
( DomainName )
VALUES ( 'loblaw' -- DomainName - varchar(100)
);
SELECT CD.*
FROM @ClientDomains AS CD
LEFT JOIN @DomainShortList AS CD2 ON CD.DomainName LIKE '%'
+ CD2.DomainName + '%'
/* OR */
SELECT CD.*
FROM @ClientDomains AS CD
LEFT JOIN @DomainShortList AS CD2 ON PATINDEX('%' + CD2.DomainName
+ '%', CD.DomainName) > 0
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2014 at 2:58 pm
My Select works fine, but when i added hte update to it, it updates the DomainRoot with the same value for all records. I am missing something but not sure what?
Update ClientDomains
Set DomainType = 'Client', DomainRoot = CD2.DomainName , Match = '10'
From (
SELECT CD.*
FROM ClientDomains AS CD
Inner JOIN DomainShortList AS CD2 ON PATINDEX('%' + CD2.DomainName + '%', CD.DomainName) > 0 AND CD.Match = 0) as CD2
March 4, 2014 at 4:46 pm
GrassHopper (3/4/2014)
My Select works fine, but when i added hte update to it, it updates the DomainRoot with the same value for all records. I am missing something but not sure what?Update ClientDomains
Set DomainType = 'Client', DomainRoot = CD2.DomainName , Match = '10'
From (
SELECT CD.*
FROM ClientDomains AS CD
Inner JOIN DomainShortList AS CD2 ON PATINDEX('%' + CD2.DomainName + '%', CD.DomainName) > 0 AND CD.Match = 0) as CD2
The update in this example should help you get what you need. Notice that in the example the 'My Weston Way' row does not get updated because there is no match because the DomainShortList has 'Weston.ca' which is not contained in the ClientDomains table. You'd have to do some more processing to find partial matches somehow.
DECLARE @clientDomains TABLE
(
DomainName VARCHAR(1000) ,
DomainRoot VARCHAR(100)
);
DECLARE @DomainShortList TABLE
(
DomainName VARCHAR(100)
);
INSERT INTO @clientDomains
( DomainName )
VALUES ( 'this is loblaw company' ),
( 'My Weston Way' ),
( 'Acme Suppliers' ),
( 'We are wesfair.ca partners' );
INSERT INTO @DomainShortList
( DomainName )
VALUES ( 'loblaw' -- DomainName - varchar(100)
),
( 'weston.ca' ),
( 'wesfair.ca' ),
( 'acme' );
UPDATE @clientDomains
SET DomainRoot = CD2.DomainName
FROM @DomainShortList AS CD2 WHERE [@clientDomains].DomainName LIKE '%'
+ CD2.DomainName + '%'
SELECT *
FROM @clientDomains AS cd;
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2014 at 7:25 am
Excellent! thanks for your help with this.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply