Filter by Comma Separated Values in IN Clause ?

The Customer table has ‘PrimaryCode’ and ‘OtherCode’ columns store Code from Codes table:Image

However some of the codes store in Customer table do not exist in Codes table.

I need to let Codes table left join Customer table, this is the first version SQL:

select * from Codes a
left outer join Customer b
on a.CodeID in (b.PrimaryCode)

That did not give me the correct outcome as query above miss out the values in OtherCodes column, so I try this:

select * from Codes a
 left outer join Customer b
 on a.CodeID in (CAST(b.PrimaryCode as varchar) + ',' + b.OtherCodes)

I try to concat the PrimaryCode + OtherCodes to have a clause like this: IN(‘1,2,3,4,5’), but that does not seems right, and I get the error message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘23,24,26,27’ to data type int.

Ok, now I know that is not right doing that, there are few solutions provided in internet (search from Google of course!). The final solution would be using LIKE keyword, then I have this which work perfect:

select * from Codes a
left outer join Customer b
on ',' + Cast(b.PrimaryCode as varchar) + ',' + b.OtherCodes + ',' LIKE '%,' + Cast(a.CodeID as varchar) + ',%'

The result I want:

Image

 

Hints to search in Google: Comma separated values in IN Clause

Keywords: LIKE

Reference: http://stackoverflow.com/questions/4613273/sql-use-comma-separated-values-with-in-clause

Advertisements

Create your own system stored procedure

Try to add our own global s.proc/system s.proc to keyboard shortcut, and need it available to all instances under one server.

Firstly we need to have a s.proc, says it select * from certain table, therefore we have below:

USE [master]
GO

Create Procedure sp_SelectAll
(
     @TableName varchar(50)    -- Receive highlighted parameter
)
As
Declare @sql varchar(500)
Select @sql = 'select * from ' + DB_NAME() + '.' + SCHEMA_NAME() + '.' + @TableName
execute (@sql)

Go

Line 1,2: sp_SelectAll need to be created under [master] since we want it available to all instances.

Line 6: An input parameter is required as later it will receive the table highlighted in query windows.

Line 10: Same table name may appear in different databases, therefore above s.proc need to recognize where the table come from. Use DB_Name() and SCHEMA_NAME() to make a distinct table call, so we will get something like

select * from Northwind.dbo.Customers

that clear enough to differentiate with

select * from pubs.dbo.Customers

 

*Note 1: In sql server 2008, or even sql server 2005, you may not able to run the sp_SelectAll in all databases, you need to run undocumented query below to fix this:


Use [master]
Go
sp_MS_marksystemobject sp_SelectAll

*Note 2: sp_SelectAll start with “sp_” prefix because we need to “cheat” sql server to treat it as system s.proc, since only system s.proc can be call from all instances, also undocumented s.proc sp_MS_marksystemobject only work for s.proc start with prefix “sp_“.

After s.proc created, allocate it with a keyboard shortcut, I’ve  allocated CTRL + 3 shortcut to this new s.proc.

sp_SelectAll with keyboard shortcut

— [ End break ] —

Reference:

  1. sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex
  2. Add keyboard query shortcuts to your SQL Query Windows

Keywords: Add customize system stored procedure to keyboard shortcut.

Add keyboard query shortcuts to your SQL Query Windows

You can add the frequently use s.procedure as your keyboard shortcut in your query analyzer(sql 2000) or sql server management studio.

For example, you always need sp_helptext to check existing s.proc in your database, you may type “sp_helptext myStoreProcedure” in your query screen, and hit “F5” to see the outcome.

Now you have faster way to do that, by attach sp_helptext to a keyboard shortcut,

  1. Go to “Tools -> Options”
  2. When Options screen pop up, go to “Environment -> Keyboard”.
  3. Key in “sp_helptext” in the keyboard shortcut you want, refer to image below, in this case is taking “CTRL + F1” shortcut.
  4. Click OK.
  5. Restart Query Analyzer/ SQL Server Management Studio to let the changes take effect.

How to use?

Highlighted the s.procedure you want to check, press “CTRL + F1”, this will call up sp_helptext shortcut that you set up in earlier steps, the content of the stored procedure will be shown immediately in Results pane.

Picture reference:

You can also add your own s.proc to keyboard shortcut, it never restrict to system s.procedure.

Keywords: Query shortcuts, keyboard shortcuts, Keyboard options, Environment settings, keyboard settings, Customize keyboard shortcuts.

SQL Select random row from table

Reference: SQL to Select a random row from a database table

Quick reference to article above:


-- Select a random row with MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1

-- Select a random row with PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

-- Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()

-- Select a random row with IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

-- Thanks Tim

-- Select a random record with Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Keyword:  random record

UDF, User Defined Functions in MSSQL

There are three types of UDF (refer to reference), however I will talk about Scalar UDF  here (Which means that function only return single value, you may refer to reference links below for other types).

UDF is quite useful for sometimes, ok, lets go to the point:

Environment:

  1. MS SQL Server 2000 or above.
  2. Northwind sample database from Microsoft.

Objective:

  • List all products in ONE row according to supplier id.

You can try it without UDF, do post your method if you know how to do that.

Here is the part of Product data:

Northwind - ProductsNorthwind – Products

First: We need to create a function to concat all product names into one line for each supplier id.


USE NORTHWIND
go

CREATE FUNCTION dbo.ConcatProductName (@sSupplierID as varchar(5))
RETURNS VARCHAR(1000)
AS
BEGIN

   DECLARE @sProduct as varchar(500)

   SET @sProduct = ''

   SELECT @sProduct = @sProduct + ProductName + ', ' FROM PRODUCTS
   WHERE SupplierID = @sSupplierID
   --	group by ProductName

   SET @sProduct = Left(@sProduct, Len(RTrim(@sProduct)) - 1)

   RETURN @sProduct
END

Second: Run and create the function above.

Third: Run the query below the check the result:


Select dbo.ConcatProductName(SupplierID) as ProductName

, SupplierID

From Products

Group by SupplierID

-- drop function ConcatProductName

Query with UDFQuery with UDF

See? It work great.

Refer to references below to know pros & cons, and more details about UDF.

References:

Northwind & pubs sample database from Microsoft.

User Defined Functions in Microsoft SQL Server 2000

User Defined Functions

Keywords: Customize function in MSSQL