SQLTeam.com | Weblogs | Forums

Check string starts with specific letter

sql2008

#1

Hello

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.

Thanks


#2

You can use LIKE
WHERE LC_ID LIKE 'F%'


#3

no , its not only using like
i need to check first if first query is not returning anything then check for like


#4

Okay, try something like this

DECLARE @RC INT = 0;
SELECT Top 1 LC_ID,ISNULL(LC_UD,0) as Record from CONTRACT where LC_ID ='F01';
SET @RC = @@ROWCOUNT;
IF @RC > 0 
BEGIN
    -- Second select
END

#5

Alternative:

select top(1)
       lc_id
      ,isnull(lc_ud,0) as record
  from contract
 where lc_id like '[FG]%'
 order by case when lc_id='F01' then 0 else 1 end
         ,lc.id
;

#6

this looks good to me..
you mentioned order by case -> how it will work ?

lc_id like '[FG]%' - > will it handle F or G senario


#7

The case statement will "float" F01 to the top.

If your table contains:
C01
B02
F01
F02
G01

the order by will make sure F01 comes first:
F01
B02
C01
F02
G01

Now if there is no F01 it will sort normally


#8

your logic is not working correctly i thing we need to put condition first in where clause and then in order by case with LC_ID like '[FG]%'


#9

My logic works exactly the way I intended and the way I thought you wanted it, when I read your initial post.

Obviously I didn't understand what it is you're trying to accomplish, so to get to a solution you can use, the best thing would be that you provide sample data and expected output. Please make at least two scenarios - one where your first query return records and one where it doesn't.