equivalent function in sql for crystal function startswith

hi experts,

has anyone of you familiar with the command startswith of crystal?  well, anyway.....i'm looking for an equivalent command in sql.

example:

in crystal

if variable starswith ['Desktop','Laptop'] then 'okay' else 'not okay'

will return

variable           result
Desktop PC      okay
Laptop Cover   okay
Desk               not okay
Lap                 not okay

thanks.

ann
wala_langAsked:
Who is Participating?
 
paeloConnect With a Mentor Commented:
There is no function such as the one you are describing in T-SQL.  There are a couple of other alternatives.  If you can identify all of the starting values reliably with a fixed-number of starting characters, you could use the LEFT function.

SELECT CASE WHEN LEFT(@var,6) IN ('Deskto','Laptop') THEN 'okay' ELSE 'not okay' END AS decision

If you are just looking to simplify queries with this condition and aren't too worried about performance, you could place the values in a temp table or a permanent table and use the following syntax:

SELECT CASE WHEN EXISTS (SELECT val FROM yourtable y WHERE @var LIKE val+'%') THEN 'okay' ELSE 'not okay' END AS decision

Otherwise, you should use the the LIKE operator or the CHARINDEX function.

-Paul.
0
 
paeloCommented:
You want the equivalent in T-SQL?

How about:

SELECT CASE WHEN @var LIKE 'Desktop%' OR @var LIKE 'Laptop%' THEN 'okay' ELSE 'not okay' END AS decision

-Paul.
0
 
wala_langAuthor Commented:
yup, i tried using like but i have to do a comparison for 10 different values and i was thinking if there's another function that i can use aside from like.

ann
0
 
paeloCommented:
You could also create a UDF to check whether its in the list.  Reduces clutter in your queries but will also constitute a performance hit.

-Paul.
0
 
wala_langAuthor Commented:
thanks paul.

ann
0
All Courses

From novice to tech pro — start learning today.