मैं अक्षरों और संख्याओं के मिश्रण से मौजूदा तालिका और क्रम में निम्नलिखित डेटा का चयन कैसे कर सकता हूं। यहाँ नमूना है ...

A-1
A-10
A-2
A-3
A-4
A-5
A-6
A-7
A-8
A-9
A-3a
A-3b
A-3c
B-1
B-10
B-11
B-12
B-12a
B-12b
B-13
B-2
B-3
B-4
B-5
B-6
B-7
B-8
B-9
1
Gahiggidy 5 मई 2016, 00:19

3 जवाब

सबसे बढ़िया उत्तर

मैं इसे एक नए उत्तर के रूप में रखता हूं, क्योंकि यह वास्तव में एक उत्तर नहीं है बल्कि विभिन्न दृष्टिकोणों की तुलना है:

निष्कर्ष:

  • एक्सएमएल को छोड़कर सभी दृष्टिकोण काफी रैखिक पैमाने पर हैं
  • एक्सएमएल छोटी पंक्ति गणना के साथ सबसे तेज़ है लेकिन उच्च पंक्ति गणना के साथ खराब हो जाता है

एक परीक्षण परिदृश्य बनाएं

CREATE TABLE #tbl (ID INT IDENTITY,sortColumn VARCHAR(100));
INSERT INTO #tbl VALUES
 ('A-1')
,('A-10')
,('A-2')
,('A-3')
,('A-4')
,('A-5')
,('A-6')
,('A-7')
,('A-8')
,('A-9')
,('A-3a')
,('A-3b')
,('A-3c')
,('B-1')
,('B-10')
,('B-11')
,('B-12')
,('B-12a')
,('B-12b')
,('B-13')
,('B-2')
,('B-3')
,('B-4')
,('B-5')
,('B-6')
,('B-7')
,('B-8')
,('A-8a')
,('B-8')
,('B-9'); --30 rows
GO 1000  -- x 1.000 = 30.000 rows

मैट का दृष्टिकोण (आवश्यक के लिए साफ)

  • 3 मील पंक्तियों पर 46 सेकंड
  • 300,000 पंक्तियों पर 4.5 सेकंड
  • 30,000 पंक्तियों पर 1.3 सेकंड
  • 3.000 पंक्तियों पर 0.7 सेकंड

कोड

SELECT ID,sortColumn
FROM
    #tbl
ORDER BY
LEFT(sortColumn,CHARINDEX('-',sortColumn) -1)
,CAST((CASE
    WHEN ISNUMERIC(SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,3)) = 1 THEN SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,3)
    WHEN ISNUMERIC(SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,2)) = 1 THEN SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,2)
    WHEN ISNUMERIC(SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,1)) = 1 THEN SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,1)
    ELSE NULL
END) AS INT)
,RIGHT(sortColumn,
    LEN(sortColumn) - 
    LEN(LEFT(sortColumn,CHARINDEX('-',sortColumn) -1)) 
    - LEN(CASE
            WHEN ISNUMERIC(SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,3)) = 1 THEN SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,3)
            WHEN ISNUMERIC(SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,2)) = 1 THEN SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,2)
            WHEN ISNUMERIC(SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,1)) = 1 THEN SUBSTRING(sortColumn,CHARINDEX('-',sortColumn) + 1,1)
            ELSE NULL
    END)
    - 1 --the '-'
),ID;

CROSS APPLYs में चरणबद्ध गणना, परिकलित स्तंभों के आधार पर छांटना

  • 3 मील पंक्तियों पर 44 सेकंड
  • 300,000 पंक्तियों पर 4.4 सेकंड
  • 30.000 पंक्तियों पर 0.9 सेकंड
  • 3.000 पंक्तियों पर 0.3 सेकंड

कोड

SELECT ID,sortColumn
FROM #tbl
CROSS APPLY(SELECT CHARINDEX('-',sortColumn) AS posMinus) AS pos
CROSS APPLY(SELECT SUBSTRING(sortColumn,1,posMinus-1) AS part1
                  ,SUBSTRING(sortColumn,posMinus+1,1000) AS part2
            ) AS parts
CROSS APPLY(SELECT ISNUMERIC(part2) AS p2isnum) AS checknum
CROSS APPLY(SELECT CASE WHEN p2isnum=1 THEN '' ELSE RIGHT(part2,1) END AS part3
                  ,CASE WHEN p2isnum=1 THEN part2 ELSE SUBSTRING(part2,1,LEN(part2)-1) END AS part2New
           ) AS partsNew
ORDER BY part1,part2new,part3,ID;

CROSS APPLYs में चरणबद्ध गणना, संयोजित गद्देदार स्ट्रिंग पर छँटाई

  • 3 मील पंक्तियों पर 42 सेकंड
  • 300,000 पंक्तियों पर 4.2 सेकंड
  • 30,000 पंक्तियों पर 0.7 सेकंड
  • 3.000 पंक्तियों पर 0.4 सेकंड

कोड

SELECT ID,sortColumn
FROM #tbl
CROSS APPLY(SELECT CHARINDEX('-',sortColumn) AS posMinus) AS pos
CROSS APPLY(SELECT SUBSTRING(sortColumn,1,posMinus-1) AS part1
                  ,SUBSTRING(sortColumn,posMinus+1,1000) AS part2
            ) AS parts
ORDER BY RIGHT('.....' + part1,5) + RIGHT('.....' + part2,5 - ISNUMERIC(RIGHT(part2,1)))
        ,ID;

एक्सएमएल के साथ विभाजन, संयोजित गद्देदार स्ट्रिंग पर छँटाई

  • 3 मील पंक्तियों पर 67 सेकंड
  • 300,000 पंक्तियों पर 6.2 सेकंड
  • 30,000 पंक्तियों पर 0.7 सेकंड
  • 3.000 पंक्तियों पर 0.3 सेकंड

कोड

SELECT ID,sortColumn
FROM
(
    SELECT CAST('<r>' + REPLACE(sortColumn,'-','</r><r>') + '</r>' AS XML) AS SortColumnSplitted
          ,*
    FROM #tbl
) AS tbl
ORDER BY RIGHT('.....' + SortColumnSplitted.value('r[1]','varchar(max)'),5) + RIGHT('.....' + SortColumnSplitted.value('r[2]','varchar(max)'),5 - ISNUMERIC(RIGHT(SortColumnSplitted.value('r[2]','varchar(max)'),1)))
        ,ID;
2
Shnugo 6 मई 2016, 01:12

मैं थॉमस से सहमत हूं, लेकिन मेरे पास सीएलआर के माध्यम से बहुत सारे नेट रेगेक्स और स्ट्रिंग फ़ंक्शन भी हैं। अन्य तकनीकें जिनका हम थोड़ा उपयोग करते हैं, वे उपयोगकर्ता परिभाषित कार्य हैं जो गैर-वांछित वर्णों को अलग करने के लिए चरित्र द्वारा चरित्र को पुनरावर्ती रूप से जाते हैं (उदाहरण के लिए संख्या की तलाश में कोई अल्फा नहीं, अल्फा की तलाश करते समय कोई संख्या नहीं)। लेकिन आपके द्वारा प्रस्तुत विशेष मामले में यदि आप जानते हैं कि प्रारूप काफी मानक होगा तो आप अपने लक्ष्य तक आसानी से पहुंचने के लिए ISNUMERIC, SUBSTRINGS, आदि के संयोजन का उपयोग कर सकते हैं। उदाहरण के लिए। यदि आप जानते हैं कि यह हमेशा होता है: अल्फा + "-" + न्यूमेरिक (1-3 अंक) + अल्फा आप निम्न कार्य कर सकते हैं और यह अल्फा को अल्फा के रूप में, संख्यात्मक के रूप में संख्यात्मक, और अल्फा को अल्फा के रूप में सॉर्ट करेगा।

DECLARE @Values AS TABLE (Value VARCHAR(5))

INSERT INTO @Values (Value)
    VALUES ('A-1')
    ,('A-10')
    ,('A-2')
    ,('A-3')
    ,('A-4')
    ,('A-5')
    ,('A-6')
    ,('A-7')
    ,('A-8')
    ,('A-9')
    ,('A-3a')
    ,('A-3b')
    ,('A-3c')
    ,('B-1')
    ,('B-10')
    ,('B-11')
    ,('B-12')
    ,('B-12a')
    ,('B-12b')
    ,('B-13')
    ,('B-2')
    ,('B-3')
    ,('B-4')
    ,('B-5')
    ,('B-6')
    ,('B-7')
    ,('B-8')
    ,('B-9')

SELECT
    *
    ,FirstAlphaSection = LEFT(Value,CHARINDEX('-',Value) -1)
    ,SecondNumericSection = CASE
       WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,3)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,3)
       WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,2)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,2)
       WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,1)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,1)
       ELSE NULL
    END
    ,ThirdAlphaSection =
       RIGHT(Value,
          LEN(Value) - 
          LEN(LEFT(Value,CHARINDEX('-',Value) -1)) 
          - LEN(CASE
                WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,3)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,3)
                WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,2)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,2)
                WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,1)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,1)
                ELSE NULL
          END)
          - 1 --the '-'
       )
FROM
    @Values
ORDER BY
    LEFT(Value,CHARINDEX('-',Value) -1)
    ,CAST((CASE
       WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,3)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,3)
       WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,2)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,2)
       WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,1)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,1)
       ELSE NULL
    END) AS INT)
    ,RIGHT(Value,
       LEN(Value) - 
       LEN(LEFT(Value,CHARINDEX('-',Value) -1)) 
       - LEN(CASE
             WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,3)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,3)
             WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,2)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,2)
             WHEN ISNUMERIC(SUBSTRING(Value,CHARINDEX('-',Value) + 1,1)) = 1 THEN SUBSTRING(Value,CHARINDEX('-',Value) + 1,1)
             ELSE NULL
       END)
       - 1 --the '-'
    )
1
Matt 5 मई 2016, 00:57

सबसे शक्तिशाली समाधान एक SQL CLR फ़ंक्शन बनाना है। हालांकि यह थोड़ा कठिन है।

एक अन्य दृष्टिकोण एक सम्मिलित/अद्यतन ट्रिगर लिख रहा है जो टीएसक्यूएल के साथ मिश्रित कॉलम में मान को विभाजित करता है और विशिष्ट सहायक कॉलम (जिसे आप सॉर्ट करने के लिए उपयोग कर सकते हैं) में तीन भागों (चरित्र, संख्या, वर्ण) को संग्रहीत करता है। अपने उदाहरणों के आधार पर, आप इस कोड की तर्ज पर विभाजन के साथ प्रयोग कर सकते हैं:

declare @value nvarchar(10) = 'B-12b';

-- first part
select substring(@value, 1, 1)

-- second part
select case when isnumeric(right(@value, 1)) = 1
    then substring(@value, 3, len(@value) - 2)
    else substring(@value, 3, len(@value) - 3)
    end

-- third part
select case when isnumeric(right(@value, 1)) = 1
    then '_'
    else right(@value, 1)
    end
1
Community 23 मई 2017, 13:34