datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to find the longest common prefix of strings?

Execute the following Microsoft SQL Server 2008 T-SQL database scripts in Query Editor to demonstrate string functions, the use of UDF to find the longest common prefix and unique suffix of an array of strings.

-- T-SQL SUBSTRING usage example - string system function - LEFT, RIGHT, ASCII

SELECT ProductNumber,

       MiddleSubstring=SUBSTRING(ProductNumber,4,5),

       ASCIIValue = ASCII(SUBSTRING(ProductNumber,2,1))

FROM AdventureWorks2008.Production.Product

ORDER BY Name

/* ProductNumber  MiddleSubstring   ASCIIValue

....

      FR-M21S-48  M21S-             82

      FR-M21S-52  M21S-             82

      FW-M423     M423              87

      HB-M243     M243              66  .... */

------------

-- T-SQL string formatting - concatenation with +

DECLARE @SSN char(9) = '123456789'

SELECT SSN=LEFT(@SSN,3)+'-'+ SUBSTRING(@SSN,4,2)+'-'+ RIGHT(@SSN,4)

-- 123-45-6789

------------

-- SQL longest common prefix - QUICK SYNTAX - Recursive CTE - CROSS APPLY usage

;WITH cteSeq(Nbr)

     AS (SELECT 1

         UNION ALL

         SELECT Nbr + 1

         FROM   cteSeq  WHERE  Nbr < 64)

SELECT PrefixLength=MAX(Nbr)

FROM   cteSeq

       CROSS APPLY (SELECT DeltaCount = Count(DISTINCT Prefix)

                    FROM   (SELECT Prefix = (SUBSTRING(Name, 1, Nbr))

                            FROM   AdventureWorks2008.Production.Product

                            WHERE  Name LIKE ('Mount%')) X) Y

WHERE  DeltaCount = 1

-- 8

------------

-- SQL find longest common prefix - find unique suffix - substring

-- SQL longest common prefix of strings

USE tempdb;

-- Create and populate table with SELECT INTO

SELECT   ID,

         ProductName = Name,

         UniquePart = CONVERT(VARCHAR(64),'')

INTO     ProdPrefix

FROM     AdventureWorks2008.Production.Product p

         INNER JOIN (SELECT   ID = ROW_NUMBER()

                                     OVER(ORDER BY SUBSTRING(ProductNumber,1, 7)),

                              Prefix = SUBSTRING(ProductNumber,1, 7)

                     FROM     AdventureWorks2008.Production.Product

                     WHERE    ListPrice > 0

                     GROUP BY SUBSTRING(ProductNumber,1,7)

                     HAVING   COUNT(* ) > 2) x

           ON SUBSTRING(p.ProductNumber,1,7) = x.Prefix

ORDER BY ID

GO

SELECT TOP(10)* FROM ProdPrefix

GO

/*

ID    ProductName             UniquePart

1     Mountain-500 Black, 40 

1     Mountain-500 Black, 42 

1     Mountain-500 Black, 44 

1     Mountain-500 Black, 48 

1     Mountain-500 Black, 52 

2     Mountain-500 Silver, 40

2     Mountain-500 Silver, 42

2     Mountain-500 Silver, 44

2     Mountain-500 Silver, 48

2     Mountain-500 Silver, 52

*/

— SQL scalar-valued user-defined function

CREATE FUNCTION fnCommonPart (@ID int)

RETURNS VARCHAR(64)

AS

BEGIN

DECLARE @i tinyint = 0, @Members int, @Ret varchar(64)

DECLARE @Name TABLE ( Seq int identity(1,1), String varchar(64))

INSERT INTO @Name SELECT ProductName FROM ProdPrefix WHERE ID = @ID

SET @Members = @@rowcount

WHILE (1 < 2)

BEGIN

  SET @i = @i+1

  IF ((SELECT max(PartCount) FROM (SELECT PartCount=COUNT(*) FROM @Name

  GROUP BY LEFT(String,@i)) x)= @Members)

  CONTINUE

  ELSE BREAK

END

IF @i = 1 SET @Ret = ''

ELSE SET @Ret= (SELECT LEFT(String, @i-1) FROM @Name WHERE Seq=1)

RETURN (@Ret)

END

GO

SELECT dbo.fnCommonPart(1)

SELECT dbo.fnCommonPart(2)

UPDATE ProdPrefix

SET UniquePart = RTRIM(LTRIM(RIGHT(ProductName, len(ProductName)-

                 len(dbo.fnCommonPart(ID)))))

GO

SELECT TOP(10)* FROM ProdPrefix

GO

/*

ID    ProductName                   UniquePart

1     Mountain-500 Black, 40        40

1     Mountain-500 Black, 42        42

1     Mountain-500 Black, 44        44

1     Mountain-500 Black, 48        48

1     Mountain-500 Black, 52        52

2     Mountain-500 Silver, 40       40

2     Mountain-500 Silver, 42       42

2     Mountain-500 Silver, 44       44

2     Mountain-500 Silver, 48       48

2     Mountain-500 Silver, 52       52

*/

DROP TABLE tempdb.dbo.ProdPrefix

DROP FUNCTION dbo.fnCommonPart

------------

 

Exam Prep 70-461
Exam 70-461