How do you search a table column containing wildcards

Transact-SQL
1

Hi All,

Can anyone advise how to return a result from a table where the column being search contains 'wildcards' ?

FYI, I use the wildcard term loosely as some true wildcards have been replaced, in this case an asterix is replaced by a small "x" at the end of a string. The table being searched contains a list of rules to convert accounts from system1 to system2, based on a concatenated search string from system1, So, if I have a search string of "ABC12349" from system1, how would I get "XYZ12340" from the following:

See fig1
Rule System2 Account
ABC123x XYZ12300
ABC1234x XYZ12340
ABC12345 XYZ12345

One important point to note.....it is common multiple rules can apply to the search string, however only 1 rule should apply, therefore rules are prioritised based on closest to a full match, as per the following examples:

See Fig2
Search System2 Account
ABC99999 No Rule
ABC12345 XYZ12345
ABC12346 XYZ12340
ABC12351 XYZ12300

Can anyone help?

I'm using Microsoft SQL Server 2014 and MS Office 2013.

Thanks in advance

Steve

2

Use the Replace Function and substitute x for % then compare the right numeric part of system 2 account to the rule.

SELECT System2Account
FROM System2
WHERE 
   Replace(Right(Rule, Len(Rule)-3), 'x', '%') LIKE Right(System2Account, Len(Rule)-3) ;
3

Since you didn't post useable sample data -- CREATE TABLE with INSERT statements -- I can't test the code below, but something like this should do what you want to do:

SELECT mt.Search, [rt.Rule] --,...
FROM main_table mt
OUTER APPLY (
    SELECT TOP (1) *
    FROM rules_table rt
    WHERE mt.Search LIKE REPLACE(rt.[Rule], 'x', '%')
    ORDER BY LEN(LEFT(rt.[Rule], CHARINDEX('x', rt.[Rule] + 'x'))) DESC
) AS oa1