Förord

Kul med nummer i Transact-SQL frågor av Vyas Kondreddi För en tid sedan så frågade en person i en Microsoft publik SQL server nyhetsgrupp hur man kan sortera bokstäver inom strängen. Till Exempel, en sträng innehåller ’CBA’, och han ville sortera dessa inom strängen så att det blev ’ABC’. Istället för att göra en procedur lösning, mest på kul, Så försökte jag att lösa det på en relativ väg (T-SQL specifik). I den här artikeln så ska jag visa hur vi kan använda en nummer tabell för att skriva den här typen av innovativa frågor.

Innehåll

Låt oss först skapa nummer tabellen kallad ’Numbers’, med en ena kolumn kallad ’Number’.
Number kolumnen är en IDENTITY kolumn med en primärnyckel och ett klustrat index baserat på den. I det här fallet så kan ett klustrat index öka prestandan på frågan, eftersom vi kommer att fråga den här nummer tabellen på ett urval av nummer.

Följande kod kommer att radera ‘Numbers’ tabellen om den redan finns, skapa den och fylla den med nummer från 1 till 8000 (Vi behöver bara 8000 nummer eftersom char/varchar variabeln eller kolumnen kan ha max 8000 tecken). Notera användandet av ”DEFAULT VALUES”. Det är så man bör sätta in rader i en tabell med en kolumn, och att den råkar vara IDENTITY kolumnen. Jag använder IDENTITY kolumnen här, just för bekvämlighet och enkelhet, och det är inget som stoppar dig från att använda en smallint kolumn och sätta in 8000 rader genom att öka en variabel.


SET NOCOUNT ON
GO

IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Numbers'
AND TABLE_SCHEMA = 'dbo'
AND TABLE_TYPE = 'BASE TABLE'
)
BEGIN
DROP TABLE dbo.Numbers
END
GO

CREATE TABLE dbo.Numbers
(
Number smallint IDENTITY(1, 1) PRIMARY KEY
)
GO

WHILE 1 = 1
BEGIN
INSERT INTO dbo.Numbers DEFAULT VALUES

IF @@IDENTITY = 8000
BEGIN
BREAK
END
END
GO

Låt oss nu titta på problemet. ”Sortera tecken inom en sträng”.

Följande script kommer att använda Numbers tabellen för att splitta en sträng till individuella tecken och skapa en tabell, från vilken den skapar en sorterat sträng genom att använda en förenad sammanslagnings fråga. Det här är ett mycket bättre sätt att ta sig an problemet, jämfört med att du implementerar en sorterings algoritm i ditt script. Varför återuppfinna hjulet när SQL server kan göra den begärda sorteringen?.


DECLARE @input varchar(100), @output varchar(100), @len smallint
SET @input = 'CDBEA'
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output + Val
FROM
(
SELECT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Val
) AS Derived

SELECT @input AS 'Original string',
@output AS 'Sorted string'


Den här iden kan utökas till andra tillfällen. Vad sägs om att plocka ut unika tecken från en given sträng? Det ger, att om 'abbcccdddd' skickas in så returneras 'abcd'. Följande script kommer att göra exakt det och använda DISTINCT som nyckelord. Igen, det här sättet använder SQL serverns förmåga att returnera enbart distinkta rader från ett givet värde. Det skulle vara en komplicerad uppgift om du skulle behöva implementera den här förmågan själv.


DECLARE @input varchar(100), @output varchar(100), @len smallint
SET @input = 'ABBCCCDDDD'
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output + Val
FROM
(
SELECT DISTINCT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Val
) AS Derived

SELECT @input AS 'Original string',
@output AS 'Sorted string with UNIQUE characters only'


Här är ett annat scenario! Vad sägs om att bara få ut numren från en given sträng? Kommer du ihåg de här mobilerna som tar ut nummer från ett SMS/Text meddelande? :-) Självklart, det här problemet kan lätt lösas med en lagrad procedur genom loopa igenom strängen från start till slut och ta bort icke numeriska tecken. Prestanda skillnader mellan dessa tillvägagångssätt sätt borde vara minimal. I alla fall, här är koden.


DECLARE @input varchar(100), @output varchar(100), @len smallint
SET @input = 'My Number is: 0771 543 2360'
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output + Val
FROM
(
SELECT TOP 100 PERCENT Number, SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Number
) AS Derived
WHERE Val LIKE '[0-9]'

SELECT @input AS 'Original string',
@output AS 'Extracted numbers'


Om du använder SQL server 2000, så kan det här skriptet smidigt göras om till User Defined Functions (UDF). Jag sa ”smidigt” eftersom, UDFs kan användas på ett flertal ställen som en kolumn listas, WHERE sats i en SELECT fråga. Här är User Defined Function skriptet.

User Defined Function 1: SortString()


IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'SortString'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
DROP FUNCTION dbo.SortString
END
GO

CREATE FUNCTION dbo.SortString
(
@input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @output varchar(8000), @len smallint
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output + Val
FROM
(
SELECT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Val
) AS Derived

RETURN @output
END
GO

SELECT dbo.SortString('911Abcdzyxfghjie999') AS 'Sorted string'
GO


User Defined Function 2: ExtractUniqueChars()


IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ExtractUniqueChars'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
DROP FUNCTION dbo.ExtractUniqueChars
END
GO

CREATE FUNCTION dbo.ExtractUniqueChars
(
@input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @output varchar(8000), @len smallint
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output + Val
FROM
(
SELECT DISTINCT TOP 100 PERCENT SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Val
) AS Derived

RETURN @output
END
GO

SELECT dbo.ExtractUniqueChars('cba abc bac') AS 'Unique characters (Sorted)'
GO


User Defined Function 3: ExtractNumbers()


IF EXISTS
(
SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'ExtractNumbers'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_TYPE = 'FUNCTION'
)
BEGIN
DROP FUNCTION dbo.ExtractNumbers
END
GO

CREATE FUNCTION dbo.ExtractNumbers
(
@input varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @output varchar(8000), @len smallint
SET @output = ''
SET @len = LEN(@input)

SELECT @output = @output + Val
FROM
(
SELECT TOP 100 PERCENT Number, SUBSTRING(@input, Number, 1) AS Val
FROM dbo.Numbers (NOLOCK)
WHERE Number <= @len
ORDER BY Number
) AS Derived
WHERE Val LIKE '[0-9]'

RETURN @output
END
GO

SELECT dbo.ExtractNumbers('My Number is: 0771 574 0609')
GO


Om du kan avvara några få kilobyte minne, så kan du skriva in Numbers tabellen till minnet, så att sidorna på den här tabellen är kvar i minnet., när den är läst till minnet. Det här är okej med mindre tabeller som ’Numbers’ tabellen, men försök inte detta med större tabeller eftersom det kan få en negativ påverkan på SQL serverns prestanda. Följande kommando kan användas för att skriva in ’Numbers’ tabellen till minnet. (Se också DBCC PINTABLE i SQL Books Online (BOL)):


EXEC sp_tableoption 'Numbers',null pintable',null true'
GO


I de flesta fallen är det kanske inte är nödvändigt att skriva tabellen till minnet, om det används flitigt och om SQL servern har tillräckligt med datacach för att hålla den här tabellen i minnet. Det här avslutar den här artikeln. Jag har koncentrerat enbart på sträng manipulering, men såklart, det är mer som kan göras med en tabell av nummer.

0 Kommentarer

Skriv en kommentar på artikeln

Ditt betyg på artikeln

UtmärktLäsvärdIntressantMindre braDålig

Kommentar: