SQL - Remove Duplicates from a Delimited String

The following is a function to remove duplicates from a delimited string.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[mga_distinct_list]
(
@List VARCHAR(MAX),
@Delim CHAR
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE @ParsedList TABLE
(
Item VARCHAR(MAX)
)
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @list = LTRIM(RTRIM(@list)) + @Delim
SET @pos = CHARINDEX(@delim, @list, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@list, @pos - 1)))
IF @list1 <> ''
INSERT INTO @ParsedList VALUES (CAST(@list1 AS VARCHAR(MAX)))
SET @list = SUBSTRING(@list, @pos+1, LEN(@list))
SET @pos = CHARINDEX(@delim, @list, 1)
END
SELECT @rlist = COALESCE(@rlist+',','') + item
FROM (SELECT DISTINCT Item FROM @ParsedList) t
RETURN @rlist
END

SQL - Format List with Commas & And

USE [######]

GO

/****** Object:  UserDefinedFunction [dbo].[format_list]    Script Date: 05/17/2012 09:39:42 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- ===========================================================================================

-- Author:        jefz

-- Create date: 5/17/2012

-- Description:   Use to turn a list that looks like this:

--                     

--                            Bob| Joe| Sally| Billy

--                     

--                      Into a list that looks like this:

--

--                            Bob, Joe, Sally and Billy

--

--                      A '|' is used to delimit the original list incase any of the names (or any

--                      string value used) includes a comma.  When the original list is created, it

--                      should be created with a '|'

-- ===========================================================================================

CREATE FUNCTION [dbo].[format_list]

(

      @list as nvarchar(max)

)

RETURNS nvarchar(max)

AS

BEGIN

      IF len(@list)-len(replace(@list, '|', '')) > 0

                 

                        BEGIN

                              select @list = reverse(@list)

                              select @list = left(@list, CHARINDEX ('|' ,@list)-1) + 'dna ' + replace(right(@list, len(@list)-CHARINDEX ('|' ,@list)), '|', ' ,')

                              select @list = reverse(@list)

                        END

                 

                  ELSE

                 

                        BEGIN

                              select @list = @list

                        END

     

                 

          RETURN @list

END

SQL - Return Multiple Records on One Line

If you have multiple records you want to appear on one line delimited by some character such as a comma, see below.

This would make:

Jones
Smith
Williams

Look like this:

Jones, Smith, Williams

DECLARE @all_names varchar(2000);

SELECT @all_names = COALESCE(@all_names + ', ', '') +

a.last_name 

FROM customers_table a

--WHERE .... (this is optional)

SELECT @all_names as 'last_names'

SQL - Zero Pad a Number

declare @somenumber int = 46;

select RIGHT(REPLICATE('0',3) + CAST(@somenumber AS NVARCHAR(3)),3)

This will take the number '46' and format it as a nvarchar to look like '046'. The number '3' in the expression specifies the number of digits you want the number to have.

SQL - Strip / Remove Lead Zeroes of a String

Cast the string to an int, then back into a nvarchar:


select CAST(CAST(substring('000422', 3, 4) as int) as nvarchar)

SQL - Capitalize the First Character of a String

UPPER(LEFT(some_string,1)) + RIGHT( some_string ,(LEN( some_string )-1))