Obviously the LIKE function doesn’t work with actual column values. Does anyone know either HOW to make it work? Or an alternative that could work?
Here is what I am trying to accomplish:
sfirstname slastname sccholdername
-----------------------------------------------
SCOTT DREWYOR KENT/MICHELLE
STEPHANIE PIERSON PIERSON/STEPHANIE
AMBER D LINDSEY MURRAY/AMBER
ALEXIA SNELL SNELL/D
JOHN MESSING MESSING/JOHN
In the above list, see how every instance the sccholdername has either the customers first or last name in it. EXCEPT for the Scott Drewyor. This could mean that the wrong credit card is attached to this persons account.
I need to be able to run a script that will identify every instance of this where the sccholdername column doesn’t have either the sfirstname or slastname in it.
I’ve tried the basics like:
select *
from customers
where sccholddername not like sfirstname
or sccholdername not like slastname
Definitely doesn’t work.
5
Please try the following solution based on tokenization.
SQL Server XQuery’s Quantified Expression does all the magic: Quantified Expressions (XQuery)
Each word separated by space is considered a token, not the entire column value!
See row #3 as a concrete example of it. Its t1(x) contains the following value:
<root>
<source>
<r>AMBER</r>
<r>D</r>
<r>LINDSEY</r>
</source>
<target>
<r>MURRAY</r>
<r>AMBER</r>
</target>
</root>
To find no match just uncomment the WHERE Result = 0 clause at the end.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, sfirstname VARCHAR(20), slastname VARCHAR(20), sccholdername VARCHAR(100));
INSERT @tbl (sfirstname, slastname, sccholdername) VALUES
('SCOTT', 'DREWYOR', 'KENT/MICHELLE'),
('STEPHANIE', 'PIERSON', 'PIERSON/STEPHANIE'),
('AMBER D', 'LINDSEY', 'MURRAY/AMBER'),
('ALEXIA', 'SNELL ', 'SNELL/D'),
('JOHN', 'MESSING', 'MESSING/JOHN');
-- DDL and sample data population, end
DECLARE @separator1 CHAR(1) = SPACE(1)
, @separator2 CHAR(1) = '/';
WITH rs AS
(
SELECT *
, x.value('some $r in /root/source/r/text()
satisfies $r = /root/target/r/text()', 'BIT') AS Result
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root>' +
'<source><r><![CDATA[' + REPLACE(sfirstname + @separator1 + slastname, @separator1, ']]></r><r><![CDATA[') +
']]></r></source>' +
'<target><r><![CDATA[' + REPLACE(sccholdername, @separator2, ']]></r><r><![CDATA[') +
']]></r></target>' +
'</root>' AS XML)) AS t1(x)
)
SELECT * FROM rs
--WHERE Result = 0;
Output
| id | sfirstname | slastname | sccholdername | Result |
|---|---|---|---|---|
| 1 | SCOTT | DREWYOR | KENT/MICHELLE | 0 |
| 2 | STEPHANIE | PIERSON | PIERSON/STEPHANIE | 1 |
| 3 | AMBER D | LINDSEY | MURRAY/AMBER | 1 |
| 4 | ALEXIA | SNELL | SNELL/D | 1 |
| 5 | JOHN | MESSING | MESSING/JOHN | 1 |
Below query should be able to help you identify the sccholdername where firstname and lastname is not correct and wrong credit card is attached to the person’s account.
SELECT * FROM customers WHERE LEFT(sccholdername, CHARINDEX('/', sccholdername) - 1) <> sfirstname AND RIGHT(sccholdername, LEN(sccholdername) - CHARINDEX('/', sccholdername)) slastname ;
Puneet Kukreja is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.