CODEWITHSUNDEEP
sqlsql-server
kiran
kiran

Reputation: 305

Check string starts with specific letter

Please help me with below query , I want to check if string is starts with 'G' or 'F' in where condition along with existing condition. here is the query

first query :- 

SELECT Top 1 LC_ID, ISNULL(LC_UD, 0) as Record 
FROM CONTRACT
WHERE LC_ID = 'F01'

output

F01 | 1 ( if available) else no record will be return.

second query:

IF LC_ID starts with 'F%' or 'G%'

How i can integrate both the query into one so that if there is no record available for 'F01' value, it will check if LC_ID starts with F & G then return output

F04 | 1

else no record will be return.

Upvotes: 3

Views: 35963

Answers (2)

Elroy Flynn
Elroy Flynn

Reputation: 3218

@gordon's answer is clever, though probably specific to Sql Server. Here's a different approach. The "Top 1" feature is also sql server, but could be avoided through additional techniques. 

SELECT Top 1 LC_ID, ISNULL(LC_UD, 0) as Record 
FROM CONTRACT
WHERE LC_ID = 'F01'
UNION
SELECT Top 1 LC_ID, ISNULL(LC_UD,0) As Record
FROM CONTRACT
WHERE NOT EXISTS (SELECT * FROM CONTRACT WHERE LC_ID = 'F01')
AND LC_ID LIKE '[FG]%'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You want to prioritize the values being returned. Because you want only one, you can do this with ORDER BY:

SELECT Top 1 LC_ID, COALESCE(LC_UD, 0) as Record 
FROM CONTRACT
WHERE LC_ID LIKE '[FG]%'
ORDER BY (CASE WHEN LC_ID = 'F01' THEN 1 ELSE 2 END);

Note: This assumes you are using SQL Server (based on the syntax).

Upvotes: 5

Related Questions