मैं अक्षरों और संख्याओं के मिश्रण से मौजूदा तालिका और क्रम में निम्नलिखित डेटा का चयन कैसे कर सकता हूं। यहाँ नमूना है ...
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
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;
मैं थॉमस से सहमत हूं, लेकिन मेरे पास सीएलआर के माध्यम से बहुत सारे नेट रेगेक्स और स्ट्रिंग फ़ंक्शन भी हैं। अन्य तकनीकें जिनका हम थोड़ा उपयोग करते हैं, वे उपयोगकर्ता परिभाषित कार्य हैं जो गैर-वांछित वर्णों को अलग करने के लिए चरित्र द्वारा चरित्र को पुनरावर्ती रूप से जाते हैं (उदाहरण के लिए संख्या की तलाश में कोई अल्फा नहीं, अल्फा की तलाश करते समय कोई संख्या नहीं)। लेकिन आपके द्वारा प्रस्तुत विशेष मामले में यदि आप जानते हैं कि प्रारूप काफी मानक होगा तो आप अपने लक्ष्य तक आसानी से पहुंचने के लिए 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 '-'
)
सबसे शक्तिशाली समाधान एक 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
संबंधित सवाल
जुड़े हुए प्रश्न
नए सवाल
sql
संरचित क्वेरी भाषा (एसक्यूएल) डेटाबेस को क्वेरी करने के लिए एक भाषा है। प्रश्नों में कोड उदाहरण, तालिका संरचना, नमूना डेटा और DBMS कार्यान्वयन के लिए एक टैग (जैसे MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, आदि) का उपयोग किया जाना चाहिए। यदि आपका प्रश्न केवल एक विशिष्ट DBMS (विशिष्ट एक्सटेंशन / सुविधाओं का उपयोग करता है) से संबंधित है, तो इसके बजाय उस DBMS के टैग का उपयोग करें। एसक्यूएल के साथ टैग किए गए सवालों के जवाब में आईएसओ / आईईसी मानक एसक्यूएल का उपयोग करना चाहिए।