|
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
------------
|
