?
Solved

equivalent function in sql for crystal function startswith

Posted on 2005-03-08
5
Medium Priority
?
912 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:wala_lang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:paelo
ID: 13491396
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
 

Author Comment

by:wala_lang
ID: 13493056
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
 
LVL 9

Accepted Solution

by:
paelo earned 80 total points
ID: 13493137
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
 
LVL 9

Expert Comment

by:paelo
ID: 13493140
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
 

Author Comment

by:wala_lang
ID: 13500784
thanks paul.

ann
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question