Monthly Archives: July 2011

SQL Searching within comma delimited string

Split string UDF example you can find here

DECLARE @ChannelNumbers TABLE(Id INT, Channel VARCHAR(255))DECLARE @SearchString VARCHAR(255)SET @SearchString = '24'INSERT INTO @ChannelNumbers        ( Id, Channel )SELECT 1, '23,24' UNION ALLSELECT 3, '23' UNION ALLSELECT 3, '24' UNION ALLSELECT 5, '123' UNION ALLSELECT 6, '124'SELECT * FROM @ChannelNumbersWHERE @SearchString IN (SELECT ChannelId FROM dbo.SplitString(Id, Channel))

Notice that query does not return 124

SQL Split comma delimited string User Defined Function

CREATE FUNCTION [dbo].[SplitStringTest](    @CommaDelimitedString varchar(2000))RETURNS @ParsedList table(    StringValue varchar(50))ASBEGIN    DECLARE @OrderID varchar(50), @Pos int    SET @CommaDelimitedString = LTRIM(RTRIM(@CommaDelimitedString))+ ','    SET @Pos = CHARINDEX(',', @CommaDelimitedString, 1)    IF REPLACE(@CommaDelimitedString, ',', '') <> ''    BEGIN        WHILE @Pos > 0        BEGIN            SET @OrderID = LTRIM(RTRIM(LEFT(@CommaDelimitedString, @Pos - 1)))            IF @OrderID <> ''            BEGIN                INSERT INTO @ParsedList (StringValue)                 VALUES (CAST(@OrderID AS varchar(50)))            END            SET @CommaDelimitedString = RIGHT(@CommaDelimitedString, LEN(@CommaDelimitedString) - @Pos)            SET @Pos = CHARINDEX(',', @CommaDelimitedString, 1)        END    END        RETURNEND

Usage

SELECT StringValue FROM  dbo.SplitStringTest('A, B, C, D')

UDF version with extra values

CREATE FUNCTION [dbo].[SplitString](    @ID INT, @CommaDelimitedString varchar(2000))RETURNS @ParsedList table(    ID INT,    ChannelID varchar(50))ASBEGIN    DECLARE @OrderID varchar(50), @Pos int    SET @CommaDelimitedString = LTRIM(RTRIM(@CommaDelimitedString))+ ','    SET @Pos = CHARINDEX(',', @CommaDelimitedString, 1)    IF REPLACE(@CommaDelimitedString, ',', '') <> ''    BEGIN        WHILE @Pos > 0        BEGIN            SET @OrderID = LTRIM(RTRIM(LEFT(@CommaDelimitedString, @Pos - 1)))            IF @OrderID <> ''            BEGIN                INSERT INTO @ParsedList (ID, ChannelID)                 VALUES (@ID, CAST(@OrderID AS varchar(50)))            END            SET @CommaDelimitedString = RIGHT(@CommaDelimitedString, LEN(@CommaDelimitedString) - @Pos)            SET @Pos = CHARINDEX(',', @CommaDelimitedString, 1)                                END    END        RETURNEND

SQL: Calculate Summary and Column Summary

DECLARE @SampleTable TABLE(Name VARCHAR(100), Amount DECIMAL(12,2))INSERT INTO @SampleTableSELECT 'Value 1', 1000 UNION ALLSELECT 'Value 1', 2000 UNION ALLSELECT 'Value 1', 3000 UNION ALLSELECT 'Value 2', 1500 UNION ALLSELECT 'Value 3', 600 UNION ALLSELECT 'Value 3', 1800

Using ROLLUP

SELECT COALESCE(Name,'Total') AS Name, SUM(Amount) AS Amount FROM @SampleTableGROUP BY NameWITH ROLLUP

Using UNION ALL

SELECT Name, SUM(Amount) AS Amount FROM @SampleTableGROUP BY NameUNION ALLSELECT 'Total',SUM(Amount) FROM @SampleTable

Source: SQL Server Curry