Solved

SQL Server Query with where cluase and wildcard

Posted on 2014-12-03
5
96 Views
Last Modified: 2014-12-07
I am trying to build a query for MS SQL server that allows a user to enter an input. Using CASE, if the user do no pass a value, then all records are shown. If they enter a value then only records with that value show up. The problem that I am running into is that when the value is blank, I use the wildcard '%' to retrieve all the records based on the Student ID column, no records are returned.
0
Comment
Question by:Benki Canoso
5 Comments
 
LVL 24

Assisted Solution

by:chaau
chaau earned 250 total points
ID: 40479746
Is Student ID column of a numeric data type? If it is a CHAR/VARCHAR/NVARCHAR type you can use the LIKE operator
0
 

Author Comment

by:Benki Canoso
ID: 40479751
It is supposed to hold numeric/null/blank data. I tried using the following,
LIKE '%'
but it did not work.
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 125 total points
ID: 40479799
Most developers I know in this situation will handle it this way, using @customer_id as an example, the below T-SQL will return all rows if @customer_id is NULL or an empty string '', and just the row for that @customer_id if it exists.
SELECT yabba, dabba, doo
FROM wherever
WHERE (customer_id = @customer_id OR ISNULL(@customer_id, '') = '')

Open in new window

0
 
LVL 24

Assisted Solution

by:chaau
chaau earned 250 total points
ID: 40479814
I guess in this case your where clause for the StudentID condition should be something like this:

DECLARE @SearchStudent VARCHAR(20)
SET @SearchStudent = '25'

SELECT * from Student
WHERE 1 = (CASE WHEN @SearchStudent = '%' THEN 1
                     WHEN ISNUMERIC(@SearchStudent) = 0 THEN 0
                     ELSE CASE WHEN StudentID = CONVERT(INT, @SearchStudent) THEN 1 ELSE 0 END END)

Open in new window

The idea in the above criteria is to use CASE operator for short-circuiting. If you just used this:
SELECT * from Student
WHERE (@SearchStudent = '%' OR ISNUMERIC(@SearchStudent) = 0 OR StudentID = CONVERT(INT, @SearchStudent))

Open in new window

Then the SQL Server would complain that the '%' would not be converted to INT
SQL Server 2012 has a TRY_CAST and TRY_CONVERT that work without errors. If you have this version of SQL Server your query will look like this:
SELECT * from Student
WHERE (@SearchStudent = '%' OR StudentID = TRY_CONVERT(INT, @SearchStudent))

Open in new window

0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 125 total points
ID: 40480260
If you want to retrieve all records you don't need a filter. Just check if the value is blank then
SELECT * FROM StudentTable

Open in new window

0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

837 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