Execute an ELSE statement from SQL data, is this possible?

Question

Suppose there are 2 tables with data as shown below:

-- Table #Detail TelNO ---------- 001xxxxx 020xxxxx 021xxxxx 800xxxxx 400xxxxx 28011111 82188888 22223333 ... ... -- Table #FeeRate Expression Price Description ---------- ------- -------------------------------------------------- 001% 10.00 International call 0[^0]% 5.00 National call 800% .00 Free call 400% .80 800 like, but caller need pay for local part ELSE,How? .20 Others/Local call 

I want to select data from two JOINed tables in dbo.FUNCTION_Match (TelNO, Expression) . ( dbo.FUNCTION_Match is a crude function that wroted matches TelNO and Expression . In this example, you can think of it as TelNO LIKE Expression ).

So the result of SQL and query

 SELECT * FROM #Detail d LEFT JOIN #FeeRate f ON d.TelNO LIKE f.Expression TelNO Expression Price Description ---------- ---------- ------- -------------------------------------------------- 001xxxxx 001% 10.00 International call 020xxxxx 0[^0]% 5.00 National call 021xxxxx 0[^0]% 5.00 National call 800xxxxx 800% .00 Free call 400xxxxx 400% .80 800 like, but caller need pay for local part 28011111 NULL NULL NULL 82188888 NULL NULL NULL 22223333 NULL NULL NULL 

The problem is obvious, local calls in #Detail cannot match Others FeeRate.

My crude FUNCTION_Match function processed several expressions like TelNO NOT LIKE '0%' AND TelNO NOT LIKE [84]00% OR TelNO LIKE '0755%' to match Others FeeRate, but it's hard to write the correct multiscreen expression to express ELSE when in the table #FeeRate has a lot of entries.

So, is there a way to implement an ELSE expression from data?

---

SQL to create sample data

 CREATE TABLE #Detail (TelNO VARCHAR(10) DEFAULT '') CREATE TABLE #FeeRate (Expression VARCHAR(20) DEFAULT '', Price NUMERIC(10,2) DEFAULT 0, Description VARCHAR(50) DEFAULT '') INSERT INTO #Detail VALUES ('001xxxxx') INSERT INTO #Detail VALUES ('020xxxxx') INSERT INTO #Detail VALUES ('021xxxxx') INSERT INTO #Detail VALUES ('800xxxxx') INSERT INTO #Detail VALUES ('400xxxxx') INSERT INTO #Detail VALUES ('28011111') INSERT INTO #Detail VALUES ('82188888') INSERT INTO #Detail VALUES ('22223333') INSERT INTO #FeeRate VALUES ('001%', 10.0, 'International call') INSERT INTO #FeeRate VALUES ('0[^0]%', 5.0, 'National call') INSERT INTO #FeeRate VALUES ('800%', 0.0, 'Free call') INSERT INTO #FeeRate VALUES ('400%', 0.8, '800 like, but caller need pay for local part') INSERT INTO #FeeRate VALUES ('ELSE,How?', 0.2, 'Others/Local call') SELECT * FROM #Detail SELECT * FROM #FeeRate SELECT * FROM #Detail d LEFT JOIN #FeeRate f ON d.TelNO LIKE f.Expression DROP TABLE #Detail DROP TABLE #FeeRate 

Answer 1

Add an extra column to the FeeRate table called Priority . Assign higher priorities for matches that should occur earlier. Make two joins to the betting table, the second of which is a left join and is looking for a match with a higher priority. If the left join is working, reject the result line:

 CREATE TABLE #FeeRate (Expression VARCHAR(20) DEFAULT '', Price NUMERIC(10,2) DEFAULT 0, Description VARCHAR(50) DEFAULT '',Priority int) INSERT INTO #FeeRate VALUES ('001%', 10.0, 'International call',5) INSERT INTO #FeeRate VALUES ('0[^0]%', 5.0, 'National call',4) INSERT INTO #FeeRate VALUES ('800%', 0.0, 'Free call',3) INSERT INTO #FeeRate VALUES ('400%', 0.8, '800 like, but caller need pay for local part',2) INSERT INTO #FeeRate VALUES ('%', 0.2, 'Others/Local call',1) SELECT * FROM #Detail d inner JOIN #FeeRate f ON d.TelNO LIKE f.Expression left join #FeeRate f_anti on d.TelNo LIKE f_anti.Expression and f_anti.Priority > f.Priority where f_anti.Price is null 

This probably also simplifies some other expressions.

Answer 2

If there is only one “other” category, store its values ​​in variables and replace them when there is no match using ISNULL :

 DECLARE @expression VARCHAR(20), @price NUMERIC (10,2), @description VARCHAR(50) SELECT @expression = Expression, @price = Price, @description = [Description] FROM #FeeRate WHERE Expression = 'ELSE,How?' SELECT d.TelNO, ISNULL(f.Expression, @expression) AS Expression, ISNULL(f.Price, @price) AS Price, ISNULL(f.[Description], @description) AS [Description] FROM #Detail d LEFT JOIN #FeeRate f ON d.TelNO LIKE f.Expression 

Answer 3

I don’t know exactly what data you have in the #Detail table, but for the above data you can do something like this:

 SELECT * FROM #Detail d LEFT JOIN #FeeRate f ON (CASE WHEN d.TelNO LIKE '%xxxxx' THEN TelNO ELSE 'ELSE,How?' END) LIKE f.Expression