SQLTeam.com | Weblogs | Forums

Alphanumeric sort

tsql

#1

Hi all. I want to populate a column in a sql table based on a complex sort of another table. I think an image would explain best what I am trying to achieve:

I have the following code :

              update Oesskattings
              set number = substring(bloknommer, patindex('%[0-9]%', bloknommer), 1+patindex('%[0-9][^0-9]%', 
              bloknommer+'x')-patindex('%[0-9]%', bloknommer))

However this does not work. Any ideas would be much appreciated.
How do I attach sample data?
Regards

EDIT :

Maybe I am over-complicating matters. A alphanumeric sort with hyphens will also work - without updating the third column. Here is my Create table and Insert statements. Hope I am doing it correct for you to use.

CREATE TABLE [dbo].[Oesskattings] (
[OesskattingsID] INT IDENTITY (1, 1) NOT NULL,
[Lidnommer] VARCHAR (50) NULL,
[Lidnaam] VARCHAR (50) NULL,
[Plaasnommer] VARCHAR (50) NULL,
[Plaasnaam] VARCHAR (50) NULL,
[Bloknommer] VARCHAR (50) NULL,
[Area] VARCHAR (50) NULL,
[Wingklas] VARCHAR (50) NULL,
[Grond] VARCHAR (50) NULL,
[Besproeiing] VARCHAR (50) NULL,
[Plantjaar] INT NULL,
[Stadium] VARCHAR (50) NULL,
[Onderstok] VARCHAR (50) NULL,
[Kultivar] VARCHAR (50) NULL,
[Opp] REAL NULL,
[Alias] VARCHAR (50) NULL,
[Aliasnaam] VARCHAR (50) NULL,
[Kontak1] VARCHAR (50) NULL,
[Kontak2] VARCHAR (50) NULL,
[Klaar_EZY] VARCHAR (50) NULL,
[Klaar_VB] VARCHAR (50) NULL,
[Masjien] VARCHAR (50) NULL,
[Prod_groep] VARCHAR (50) NULL,
[e_pos_doc] VARCHAR (50) NULL,
[e_pos_har] VARCHAR (50) NULL,
[e_pos_rem] VARCHAR (50) NULL,
[Doelwit] VARCHAR (50) NULL,
[Oesmetode] VARCHAR (50) NULL,
[Snoeimetode] VARCHAR (50) NULL,
[Ton_min5] REAL NULL,
[Ton_min4] REAL NULL,
[Ton_min3] REAL NULL,
[Ton_min2] REAL NULL,
[Ton_min1] REAL NULL,
[Ton_huidig] REAL NULL,
[Tonha_min5] REAL NULL,
[Tonha_min4] REAL NULL,
[Tonha_min3] REAL NULL,
[Tonha_min2] REAL NULL,
[Tonha_min1] REAL NULL,
[Tonha_huidig] REAL NULL,
[Ton_skat_hand] REAL NULL,
[Ton_skat_masjien] REAL NULL,
[Ton_skat_totaal] REAL NULL,
[Alfasorteer1] VARCHAR (50) NULL,
[Plaasblok] VARCHAR (50) NULL,
[Kultklas] VARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([OesskattingsID] ASC)
);

My insert commands :

SET IDENTITY_INSERT [dbo].[Oesskattings] ON
INSERT INTO [dbo].[Oesskattings] ([OesskattingsID], [Lidnommer], [Lidnaam], [Plaasnommer], [Plaasnaam], [Bloknommer], [Area], [Wingklas], [Grond], [Besproeiing], [Plantjaar], [Stadium], [Onderstok], [Kultivar], [Opp], [Alias], [Aliasnaam], [Kontak1], [Kontak2], [Klaar_EZY], [Klaar_VB], [Masjien], [Prod_groep], [e_pos_doc], [e_pos_har], [e_pos_rem], [Doelwit], [Oesmetode], [Snoeimetode], [Ton_min5], [Ton_min4], [Ton_min3], [Ton_min2], [Ton_min1], [Ton_huidig], [Tonha_min5], [Tonha_min4], [Tonha_min3], [Tonha_min2], [Tonha_min1], [Tonha_huidig], [Ton_skat_hand], [Ton_skat_masjien], [Ton_skat_totaal], [Alfasorteer1], [Plaasblok], [Kultklas]) VALUES (15, N'1023-AUR', N'A.G.F. AURET', N'3518', N'DRAAIRIVIER', N'1-DRA', N'DRAAIRIVIER', N'KULT', N'ROOI SAND', N'DRUP', 2000, N'BEAR', N'RICHTER 99', N'SHZ', 4.13, N'WOLAUR', N'WOLFIE AURET', N'WOLFIE AURET-082 8966 287', N'', N'YES', NULL, N'AURW', N'AURW', N'wolfiewp@yahoo.co.uk ; 27828966287@2way.co.za', N'', N'', N'Wyn', N'MASJIEN', N'Hand gesnoei', 0, 61.74, 64.89, 71.18, 28.44, 47.2, 0, 14.95, 15.71, 17.23, 6.89, 11.43, 0, 60, 60, N'1', N'35181-DRA', N'SHZKULT')
INSERT INTO [dbo].[Oesskattings] ([OesskattingsID], [Lidnommer], [Lidnaam], [Plaasnommer], [Plaasnaam], [Bloknommer], [Area], [Wingklas], [Grond], [Besproeiing], [Plantjaar], [Stadium], [Onderstok], [Kultivar], [Opp], [Alias], [Aliasnaam], [Kontak1], [Kontak2], [Klaar_EZY], [Klaar_VB], [Masjien], [Prod_groep], [e_pos_doc], [e_pos_har], [e_pos_rem], [Doelwit], [Oesmetode], [Snoeimetode], [Ton_min5], [Ton_min4], [Ton_min3], [Ton_min2], [Ton_min1], [Ton_huidig], [Tonha_min5], [Tonha_min4], [Tonha_min3], [Tonha_min2], [Tonha_min1], [Tonha_huidig], [Ton_skat_hand], [Ton_skat_masjien], [Ton_skat_totaal], [Alfasorteer1], [Plaasblok], [Kultklas]) VALUES (16, N'1023-AUR', N'A.G.F. AURET', N'3518', N'DRAAIRIVIER', N'3-DRA', N'DRAAIRIVIER', N'BULK', N'LEEMSAND', N'DRUP', 1990, N'BEAR', N'RICHTER 99', N'DAT', 1.7, N'WOLAUR', N'WOLFIE AURET', N'WOLFIE AURET-082 8966 287', N'', N'YES', NULL, N'AURW', N'AURW', N'wolfiewp@yahoo.co.uk ; 27828966287@2way.co.za', N'', N'', N'Wyn', N'MASJIEN', N'Hand gesnoei', 0, 60.96, 44.14, 27.81, 19.49, 27.56, 0, 35.86, 25.96, 16.36, 11.46, 16.21, 20, 0, 20, N'3', N'35183-DRA', N'DATBULK')
INSERT INTO [dbo].[Oesskattings] ([OesskattingsID], [Lidnommer], [Lidnaam], [Plaasnommer], [Plaasnaam], [Bloknommer], [Area], [Wingklas], [Grond], [Besproeiing], [Plantjaar], [Stadium], [Onderstok], [Kultivar], [Opp], [Alias], [Aliasnaam], [Kontak1], [Kontak2], [Klaar_EZY], [Klaar_VB], [Masjien], [Prod_groep], [e_pos_doc], [e_pos_har], [e_pos_rem], [Doelwit], [Oesmetode], [Snoeimetode], [Ton_min5], [Ton_min4], [Ton_min3], [Ton_min2], [Ton_min1], [Ton_huidig], [Tonha_min5], [Tonha_min4], [Tonha_min3], [Tonha_min2], [Tonha_min1], [Tonha_huidig], [Ton_skat_hand], [Ton_skat_masjien], [Ton_skat_totaal], [Alfasorteer1], [Plaasblok], [Kultklas]) VALUES (17, N'1023-AUR', N'A.G.F. AURET', N'3518', N'DRAAIRIVIER', N'5-DRA', N'DRAAIRIVIER', N'BULK', N'LEEMSAND', N'DRUP', 2011, N'BEAR', N'RAMSEY', N'CHB', 5.5, N'WOLAUR', N'WOLFIE AURET', N'WOLFIE AURET-082 8966 287', N'', N'YES', NULL, N'AURW', N'AURW', N'wolfiewp@yahoo.co.uk ; 27828966287@2way.co.za', N'', N'', N'Wine', N'MASJIEN', N'Hand gesnoei', 0, 0, 44.9, 50.13, 37.62, 39.66, 0, 0, 8.16, 9.11, 6.84, 7.21, 0, 50, 50, N'5', N'35185-DRA', N'CHBBULK')
INSERT INTO [dbo].[Oesskattings] ([OesskattingsID], [Lidnommer], [Lidnaam], [Plaasnommer], [Plaasnaam], [Bloknommer], [Area], [Wingklas], [Grond], [Besproeiing], [Plantjaar], [Stadium], [Onderstok], [Kultivar], [Opp], [Alias], [Aliasnaam], [Kontak1], [Kontak2], [Klaar_EZY], [Klaar_VB], [Masjien], [Prod_groep], [e_pos_doc], [e_pos_har], [e_pos_rem], [Doelwit], [Oesmetode], [Snoeimetode], [Ton_min5], [Ton_min4], [Ton_min3], [Ton_min2], [Ton_min1], [Ton_huidig], [Tonha_min5], [Tonha_min4], [Tonha_min3], [Tonha_min2], [Tonha_min1], [Tonha_huidig], [Ton_skat_hand], [Ton_skat_masjien], [Ton_skat_totaal], [Alfasorteer1], [Plaasblok], [Kultklas]) VALUES (18, N'1023-AUR', N'A.G.F. AURET', N'3518', N'DRAAIRIVIER', N'6-DRA', N'DRAAIRIVIER', N'BULK', N'LEEMSAND', N'DRUP', 1988, N'BEAR', N'RICHTER 99', N'COL', 1.75, N'WOLAUR', N'WOLFIE AURET', N'WOLFIE AURET-082 8966 287', N'', N'NO', NULL, N'AURW', N'AURW', N'wolfiewp@yahoo.co.uk ; 27828966287@2way.co.za', N'', N'', N'Wyn', N'MASJIEN', N'Hand gesnoei', 0, 44.96, 69.11, 64.2, 10.7, 63.68, 0, 25.69, 39.49, 36.69, 6.11, 36.39, 0, 40, 40, N'6', N'35186-DRA', N'COLBULK')
INSERT INTO [dbo].[Oesskattings] ([OesskattingsID], [Lidnommer], [Lidnaam], [Plaasnommer], [Plaasnaam], [Bloknommer], [Area], [Wingklas], [Grond], [Besproeiing], [Plantjaar], [Stadium], [Onderstok], [Kultivar], [Opp], [Alias], [Aliasnaam], [Kontak1], [Kontak2], [Klaar_EZY], [Klaar_VB], [Masjien], [Prod_groep], [e_pos_doc], [e_pos_har], [e_pos_rem], [Doelwit], [Oesmetode], [Snoeimetode], [Ton_min5], [Ton_min4], [Ton_min3], [Ton_min2], [Ton_min1], [Ton_huidig], [Tonha_min5], [Tonha_min4], [Tonha_min3], [Tonha_min2], [Tonha_min1], [Tonha_huidig], [Ton_skat_hand], [Ton_skat_masjien], [Ton_skat_totaal], [Alfasorteer1], [Plaasblok], [Kultklas]) VALUES (20, N'1023-AUR', N'A.G.F. AURET', N'3921B', N'BOPLAAS', N'1-BOP', N'DRAAIRIVIER', N'BULK', N'KAROOGROND', N'DRUP', 2007, N'BEAR', N'RAMSEY', N'CHB', 4.31, N'WOLAUR', N'WOLFIE AURET', N'WOLFIE AURET-082 8966 287', N'', N'YES', NULL, N'AURW', N'AURW', N'wolfiewp@yahoo.co.uk ; 27828966287@2way.co.za', N'', N'', N'Wyn', N'MASJIEN', N'Hand gesnoei', 0, 108.85, 102.14, 26.45, 38.13, 39.34, 0, 25.26, 23.7, 6.14, 8.85, 9.13, 0, 80, 80, N'1', N'3921B1-BOP', N'CHBBULK')
INSERT INTO [dbo].[Oesskattings] ([OesskattingsID], [Lidnommer], [Lidnaam], [Plaasnommer], [Plaasnaam], [Bloknommer], [Area], [Wingklas], [Grond], [Besproeiing], [Plantjaar], [Stadium], [Onderstok], [Kultivar], [Opp], [Alias], [Aliasnaam], [Kontak1], [Kontak2], [Klaar_EZY], [Klaar_VB], [Masjien], [Prod_groep], [e_pos_doc], [e_pos_har], [e_pos_rem], [Doelwit], [Oesmetode], [Snoeimetode], [Ton_min5], [Ton_min4], [Ton_min3], [Ton_min2], [Ton_min1], [Ton_huidig], [Tonha_min5], [Tonha_min4], [Tonha_min3], [Tonha_min2], [Tonha_min1], [Tonha_huidig], [Ton_skat_hand], [Ton_skat_masjien], [Ton_skat_totaal], [Alfasorteer1], [Plaasblok], [Kultklas]) VALUES (21, N'1023-AUR', N'A.G.F. AURET', N'3921B', N'BOPLAAS', N'10-BOP', N'DRAAIRIVIER', N'BULK', N'LEEMSAND', N'MIKRO', 1993, N'BEAR', N'RICHTER 99', N'COL', 4.5, N'WOLAUR', N'WOLFIE AURET', N'WOLFIE AURET-082 8966 287', N'', N'YES', NULL, N'AURW', N'AURW', N'wolfiewp@yahoo.co.uk ; 27828966287@2way.co.za', N'', N'', N'Wyn', N'MASJIEN', N'Hand gesnoei', 0, 37.16, 46.53, 75.32, 0, 34.86, 0, 8.26, 10.34, 16.74, 0, 7.75, 0, 30, 30, N'10', N'3921B10-BOP', N'COLBULK')
INSERT INTO [dbo].[Oesskattings] ([OesskattingsID], [Lidnommer], [Lidnaam], [Plaasnommer], [Plaasnaam], [Bloknommer], [Area], [Wingklas], [Grond], [Besproeiing], [Plantjaar], [Stadium], [Onderstok], [Kultivar], [Opp], [Alias], [Aliasnaam], [Kontak1], [Kontak2], [Klaar_EZY], [Klaar_VB], [Masjien], [Prod_groep], [e_pos_doc], [e_pos_har], [e_pos_rem], [Doelwit], [Oesmetode], [Snoeimetode], [Ton_min5], [Ton_min4], [Ton_min3], [Ton_min2], [Ton_min1], [Ton_huidig], [Tonha_min5], [Tonha_min4], [Tonha_min3], [Tonha_min2], [Tonha_min1], [Tonha_huidig], [Ton_skat_hand], [Ton_skat_masjien], [Ton_skat_totaal], [Alfasorteer1], [Plaasblok], [Kultklas]) VALUES (22, N'1023-AUR', N'A.G.F. AURET', N'3921B', N'BOPLAAS', N'2-BOP', N'DRAAIRIVIER', N'BULK', N'KAROOGROND', N'DRUP', 2007, N'BEAR', N'RAMSEY', N'CHB', 4.31, N'WOLAUR', N'WOLFIE AURET', N'WOLFIE AURET-082 8966 287', N'', N'NO', NULL, N'AURW', N'AURW', N'wolfiewp@yahoo.co.uk ; 27828966287@2way.co.za', N'', N'', N'Wyn', N'MASJIEN', N'Hand gesnoei', 0, 73.35, 89.52, 94.01, 44.9, 0, 0, 17.02, 20.77, 21.81, 10.42, 0, 0, 50, 50, N'2', N'3921B2-BOP', N'CHBBULK')
INSERT INTO [dbo].[Oesskattings] ([OesskattingsID], [Lidnommer], [Lidnaam], [Plaasnommer], [Plaasnaam], [Bloknommer], [Area], [Wingklas], [Grond], [Besproeiing], [Plantjaar], [Stadium], [Onderstok], [Kultivar], [Opp], [Alias], [Aliasnaam], [Kontak1], [Kontak2], [Klaar_EZY], [Klaar_VB], [Masjien], [Prod_groep], [e_pos_doc], [e_pos_har], [e_pos_rem], [Doelwit], [Oesmetode], [Snoeimetode], [Ton_min5], [Ton_min4], [Ton_min3], [Ton_min2], [Ton_min1], [Ton_huidig], [Tonha_min5], [Tonha_min4], [Tonha_min3], [Tonha_min2], [Tonha_min1], [Tonha_huidig], [Ton_skat_hand], [Ton_skat_masjien], [Ton_skat_totaal], [Alfasorteer1], [Plaasblok], [Kultklas]) VALUES (23, N'1023-AUR', N'A.G.F. AURET', N'3921B', N'BOPLAAS', N'3-BOP', N'DRAAIRIVIER', N'BULK', N'KAROOGROND', N'VLOED', 1984, N'BEAR', N'RICHTER 99', N'COL', 2.51, N'WOLAUR', N'WOLFIE AURET', N'WOLFIE AURET-082 8966 287', N'', N'NO', NULL, N'AURW', N'AURW', N'wolfiewp@yahoo.co.uk ; 27828966287@2way.co.za', N'', N'', N'Wyn', N'MASJIEN', N'Hand gesnoei', 0, 33.54, 41.24, 59.93, 27, 0, 0, 13.36, 16.43, 23.88, 10.76, 0, 0, 30, 30, N'3', N'3921B3-BOP', N'COLBULK')
SET IDENTITY_INSERT [dbo].[Oesskattings] OFF

Hope this helps.Regards.


#2

Not at all. What's best for us is CREATE TABLE with INSERT statements so that we can create sample data without having to type it in ourselves, I, for one, don't have time to hand-code sample data for you.


#3

In addition to Scott's comments... Keep 2 things in mind...

  1. The order in which you insert items into a table has no relevance... at all... Relational tables are, by definition, unordered.
    so you'll need a separate column to actually maintain the sort... and INENTITY, SEQUENSE or ROW_NUMBER()...

  2. This is no easy feat that you are attempting... The code gets VERY complex VERY quickly. I've done it before, just to see if I could but it's not an exercise I care to repeat.

I'll see if I still have the code... I'll post it if I find it.


#4

I can't put my hands on the code I was thinking of... but this is a simplified version that I provided on SO not too long ago... Please note that their requirement didn't require a solution that accounted for numbers in multiple locations in the string, which allowed for a simpler solution.

Of course, looking the the not data in your post, you don't require it either... So this should work for you as well.

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    SomeString VARCHAR(100)
    );
INSERT #TestData(SomeString) VALUES
    ('BQS 1 (CP)'),
    ('BQS 2 (CP)'),
    ('BQS 3 (CP)'),
    ('BQS 5 (PB1)'),
    ('CAPSULE FILLING 3 ZRO (PB4)'),
    ('CAPSULE FILLING 6 A150T(PB4)'),
    ('COMPRESSION 1 (PB4)'),
    ('COMPRESSION 3 (PB4)'),
    ('COMPRESSION 4 (PB4 EX)'),
    ('COMPRESSION 5 (PB4 EX)'),
    ('COMPRESSION 12(PB4)'),
    ('PHARMA PACK 1 (CP)'),
    ('PHARMA PACK 2 (CP)'),
    ('PP III (CP)');

--====================================================================

-- option 1...
SELECT
    Option_1 = td.SomeString
FROM
    #TestData td
    CROSS APPLY ( VALUES (NULLIF(PATINDEX('%[0-9]%', td.SomeString), 0)) ) x1 (N)
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX('(', REPLACE(td.SomeString, ' ', '('), x1.N), 0)) ) x2 (P)
    CROSS APPLY ( VALUES (LEFT(td.SomeString, ISNULL(x1.N - 2, 100))) ) s1 (Sort1)
    CROSS APPLY ( VALUES (TRY_CAST(SUBSTRING(td.SomeString, ISNULL(x1.N, 1), ISNULL(x2.P - x1.N, 100)) AS INT)) ) s2 (Sort2)
ORDER BY
    s1.Sort1,
    s2.Sort2;

-- option 2...
SELECT 
    OPTION_2 = td.SomeString
FROM
    #TestData td
    CROSS APPLY ( VALUES (NULLIF(PATINDEX('%[0-9]%', td.SomeString), 0)) ) x (N)
    CROSS APPLY ( VALUES (LEFT(td.SomeString, ISNULL(x.N - 2, 100))) ) s1 (Sort1)
    CROSS APPLY ( VALUES (CASE 
                            WHEN SUBSTRING(td.SomeString, x.N, 5) NOT LIKE '%[^0-9]%' THEN CAST(SUBSTRING(td.SomeString, x.N, 5) AS INT) 
                            WHEN SUBSTRING(td.SomeString, x.N, 4) NOT LIKE '%[^0-9]%' THEN CAST(SUBSTRING(td.SomeString, x.N, 4) AS INT)
                            WHEN SUBSTRING(td.SomeString, x.N, 3) NOT LIKE '%[^0-9]%' THEN CAST(SUBSTRING(td.SomeString, x.N, 3) AS INT)
                            WHEN SUBSTRING(td.SomeString, x.N, 2) NOT LIKE '%[^0-9]%' THEN CAST(SUBSTRING(td.SomeString, x.N, 2) AS INT)
                            WHEN SUBSTRING(td.SomeString, x.N, 1) NOT LIKE '%[^0-9]%' THEN CAST(SUBSTRING(td.SomeString, x.N, 1) AS INT)
                        END) ) s2 (Sort2)
ORDER BY
    s1.Sort1,
    s2.Sort2;

Results:

Option_1
----------------------------------------
BQS 1 (CP)
BQS 2 (CP)
BQS 3 (CP)
BQS 5 (PB1)
CAPSULE FILLING 3 ZRO (PB4)
CAPSULE FILLING 6 A150T(PB4)
COMPRESSION 1 (PB4)
COMPRESSION 3 (PB4)
COMPRESSION 4 (PB4 EX)
COMPRESSION 5 (PB4 EX)
COMPRESSION 12(PB4)
PHARMA PACK 1 (CP)
PHARMA PACK 2 (CP)
PP III (CP)

OPTION_2
-----------------------------------------
BQS 1 (CP)
BQS 2 (CP)
BQS 3 (CP)
BQS 5 (PB1)
CAPSULE FILLING 3 ZRO (PB4)
CAPSULE FILLING 6 A150T(PB4)
COMPRESSION 1 (PB4)
COMPRESSION 3 (PB4)
COMPRESSION 4 (PB4 EX)
COMPRESSION 5 (PB4 EX)
COMPRESSION 12(PB4)
PHARMA PACK 1 (CP)
PHARMA PACK 2 (CP)
PP III (CP)

#5

Assigning value to IDENTITY column in target table? Well ... I use a SORT on INSERT for that purpose, maybe I'm kidding myself? :grin:


#6

Thank you for replying Scott/Jason/Kirsten . Please see my edit.


#7

There is NO guarantee that SQL honor the order when inserting into a table (if I'm incorrect please direct me to a reliable source), even when an identity column is on the table.

I agree that it "usually" will... but selecting a from a single table, without an order by clause, will "usually " return rows in the order of the clustered index... but no guarantee of that happening every time.


#8

Not sure what you have said is the thing I'm referring to, but there is the following in the SQL DOCs

https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql

"INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted"

I use this to insert into #TEMP with an IDENTITY ID column and ORDER BY on columns not included in the #TEMP itself, so that I don't have to persist them until the SELECT on the #TEMP used for "results" purposes - I can just ORDER BY on the ID column at that point (and have it as the Clustered Index Key too if I like, which obviously makes the final ID Sort efficient)


#9

I've done it myself and never saw it not work... But... lots of people said the same thing prior to 2008 (if I recall correctly) code that had been relying on the "top 100% ... ORDER BY " trick in views... learned a hard lesson.

But... If MS will guarantee the identity order... sweet!