SQL Server Performance

Natural Alphanumeric sorting

Discussion in 'General Developer Questions' started by bagman3rd, Aug 16, 2005.

  1. bagman3rd New Member

    This is a problem for almost every project that I work on. I get a list of location for water and/soil sampling which look like this

    mw-1
    mw-2
    mw-3
    mw-4
    mw-10
    mw-11
    mw-20
    mw-31

    so I must use a character/text field. But when I sort this varchar filed, I get

    mw-1
    mw-10
    mw-11
    mw-2
    mw-20
    mw-3
    mw-31
    mw-4

    but I want a natural alphanumeric sorting order, hence

    mw-1
    mw-2
    mw-3
    mw-4
    mw-10
    mw-11
    mw-20
    mw-31

    How can I acheive this in SQL?

    Thanks.

    Archer
  2. Luis Martin Moderator

    Just a way to work with:

    SELECT val from prueba order by LEFT(val, 3), convert (integer,SUBSTRING(val,4,2))

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. Madhivanan Moderator


    This also work correctly

    SELECT location from yourtable order by len(location ),location


    Madhivanan

    Failing to plan is Planning to fail
  4. FrankKalis Moderator

    You know that the "best" solution would be to split this into two columns? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  5. Madhivanan Moderator

    >>You know that the "best" solution would be to split this into two columns?

    Otherwise have only one int column and add mw- as prefix to display it


    Madhivanan

    Failing to plan is Planning to fail
  6. bagman3rd New Member

    Actually it is not as easy as that. Sorry that I was not more specific in my first post, but I have no control over the naming of the sampling locations. What I need is this:

    http://us2.php.net/natsort

    natsort - This function implements a sort algorithm that orders alphanumeric strings in the way a human being would while maintaining key/value associations. This is described as a "natural ordering".

    But I need it in an SQL script. After numerous unsuccessfull attempts on different user groups, I created it myself. The text string must start with text, not a
    number, but this will order by the first text chunk, then the next number
    chunk as an integer, then the rest of the string as text

    select fieldid
    from tblsamples
    order by LEFT(fieldid, PATINDEX('%[0-9]%', fieldid) - 1),
    cast(left (right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1
    ),
    case
    when patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
    PATINDEX('%[0-9]%', fieldid) + 1 )) = 0
    then patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
    PATINDEX('%[0-9]%', fieldid) + 1 ))
    else
    patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
    fieldid) + 1 )) -1
    end) as int),
    right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )

    I will try to expand it to handle the first 5 chunks instead of the just the
    first 3.

    Tnanks for the help.

    Archer
  7. Madhivanan Moderator

    Did you try the query I suggested?


    Madhivanan

    Failing to plan is Planning to fail
  8. FrankKalis Moderator

    Just to illustrate Madhivanan's point


    DECLARE @table TABLE (mycol VARCHAR(10))
    INSERT INTO @table SELECT 'mw-1'
    UNION ALL SELECT 'mw-10'
    UNION ALL SELECT 'mw-11'
    UNION ALL SELECT 'mw-2'
    UNION ALL SELECT 'mw-20'
    UNION ALL SELECT 'mw-3'
    UNION ALL SELECT 'mw-31'
    UNION ALL SELECT 'mw-4'

    SELECT mycol
    FROM @table
    ORDER BY LEN(mycol),mycol

    mycol
    ----------
    mw-1
    mw-2
    mw-3
    mw-4
    mw-10
    mw-11
    mw-20
    mw-31

    (8 row(s) affected)

    Isn't it this what you're looking for?


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  9. FrankKalis Moderator

    ...at least if you can ensure that the "prefix" mw is always the same and won't change at all.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  10. Adriaan New Member

    Okay, what about varying prefixes, and varying lengths ... look at this list:

    lp-9
    m-9000
    m-987879
    mw-12
    mwi-1

    Using the LEN(COL), COL ordering this list would come out as

    lp-9
    mw-12
    mwi-1
    m-9000
    m-987879

    So we need to (1) neutralize the number parts and sort on alpha first, and (2) neutralize the alpha parts and sort on number second.

    Here's a script that should be transformed into a UDF to return the neutralized ordering string:

    -- The first part of this script is just so we have some data to process ...

    CREATE TABLE #TMP (TestValue VARCHAR(10), OrderBy VARCHAR(20))
    INSERT INTO #TMP (TestValue) VALUES ('m-987879')
    INSERT INTO #TMP (TestValue) VALUES ('m-9000')
    INSERT INTO #TMP (TestValue) VALUES ('lp-9')
    INSERT INTO #TMP (TestValue) VALUES ('mwi-1')
    INSERT INTO #TMP (TestValue) VALUES ('mw-12')

    DECLARE @TestValue VARCHAR(10)
    DECLARE @MirrorNumber VARCHAR(10), @MirrorAlpha VARCHAR(10)
    DECLARE @Char CHAR(1), @Len INT, @Runner INT

    DECLARE test CURSOR FORWARD_ONLY
    FOR
    SELECT TestValue FROM #TMP
    OPEN test

    FETCH NEXT FROM test INTO @TestValue
    WHILE @@FETCH_STATUS = 0
    BEGIN

    -- Now this would be the function body!

    SET @MirrorNumber = ''
    SET @MirrorAlpha = ''

    SET @Len = LEN(@TestValue)
    SET @Runner = 1
    WHILE @Runner <= @Len
    BEGIN
    SET @Char = SUBSTRING(@TestValue, @Runner, 1)
    SELECT @MirrorNumber = @MirrorNumber + CASE WHEN ISNUMERIC(@Char) = 1 THEN @Char ELSE '0' END,
    @MirrorAlpha = @MirrorAlpha + CASE WHEN ISNUMERIC(@Char) = 1 THEN '0' ELSE @Char END
    SET @Runner = @Runner + 1
    END

    -- Obviously a function would just return the combined sorting string.

    UPDATE #TMP SET OrderBy = (@MirrorAlpha + @MirrorNumber) WHERE #TMP.TestValue = @TestValue
    FETCH NEXT FROM test INTO @TestValue

    END

    CLOSE test
    DEALLOCATE test

    SELECT * FROM #TMP
    ORDER BY LEN(TestValue), TestValue

    SELECT * FROM #TMP
    ORDER BY OrderBy

    DROP TABLE #TMP
  11. FrankKalis Moderator

    What about


    CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
    RETURNS BIGINT
    BEGIN
    DECLARE @pos INT
    SET @Pos = PATINDEX('%[^0-9]%',@Input)
    WHILE @Pos > 0
    BEGIN
    SET @Input = STUFF(@Input,@pos,1,'')
    SET @Pos = PATINDEX('%[^0-9]%',@Input)
    END
    RETURN @Input
    END
    GO

    CREATE FUNCTION dbo.RemoveNumbers(@Input varchar(1000))
    RETURNS VARCHAR(1000)
    BEGIN
    DECLARE @pos INT
    SET @Pos = PATINDEX('%[0-9]%',@Input)
    WHILE @Pos > 0
    BEGIN
    SET @Input = STUFF(@Input,@pos,1,'')
    SET @Pos = PATINDEX('%[0-9]%',@Input)
    END
    RETURN @Input
    END
    GO

    SET NOCOUNT ON
    CREATE TABLE #TMP (TestValue VARCHAR(10))
    INSERT INTO #TMP (TestValue) VALUES ('m-987879')
    INSERT INTO #TMP (TestValue) VALUES ('m-9000')
    INSERT INTO #TMP (TestValue) VALUES ('lp-9')
    INSERT INTO #TMP (TestValue) VALUES ('mwi-1')
    INSERT INTO #TMP (TestValue) VALUES ('mw-12')
    SET NOCOUNT OFF

    SELECT * FROM #TMP
    ORDER BY dbo.RemoveNumbers(TestValue), dbo.RemoveChars(TestValue)


    DROP TABLE #TMP
    DROP FUNCTION dbo.RemoveNumbers, dbo.RemoveChars

    TestValue
    ----------
    lp-9
    m-9000
    m-987879
    mw-12
    mwi-1

    (5 row(s) affected)



    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  12. Adriaan New Member

    Frank,<br /><br />I tested both for sorting longer data, like 250 characters from a remarks field, for 100 rows with data. Using your functions, it took about 20 seconds. Using mine, it was about 2 seconds.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Tested first in a batch with yours before mine. Then another batch with mine before yours. Same difference.<br /><br />Not sure if calling two functions is killing performance, or that the STUFF operation is so much slower than rebuilding the string from position 1 ...<br /><br />Adriaan<br /><br />EDIT:<br />Here's my function:<br /><pre id="code"><font face="courier" size="2" id="code">CREATE FUNCTION dbo.test<br />(@TestValue VARCHAR(250))<br />RETURNS VARCHAR(500)<br />BEGIN<br />DECLARE @MirrorNumber VARCHAR(250), @MirrorAlpha VARCHAR(250)<br />DECLARE @Len INT, @Runner INT, @Char VARCHAR(1)<br /><br />SET @MirrorNumber = ''<br />SET @MirrorAlpha = ''<br />SET @Len = LEN(@TestValue)<br />SET @Runner = 1<br />WHILE @Runner &lt;= @Len<br />BEGIN<br />SET @Char = SUBSTRING(@TestValue, @Runner, 1)<br />SELECT @MirrorNumber = @MirrorNumber + CASE WHEN ISNUMERIC(@Char) = 1 THEN @Char ELSE '0' END,<br />@MirrorAlpha = @MirrorAlpha + CASE WHEN ISNUMERIC(@Char) = 1 THEN '0' ELSE @Char END<br />SET @Runner = @Runner + 1<br />END<br />RETURN (@MirrorAlpha + @MirrorNumber)<br /><br />END</font id="code"></pre id="code">
  13. FrankKalis Moderator

    You should be able to speed this further up by using a numbers table instead of looping through with WHILE. So something like<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT @MirrorNumber = SUBSTRING(@TestValue, Number, 1)...<br /> , @MirrorAlpha = SUBSTRING....<br /> FROM master..spt_values<br /> WHERE Type='P' AND Number BETWEEN 1 AND LEN(@TestValue)<br /></font id="code"></pre id="code"><br />And now for the tricky part here. How will your function handle such data?<br /><pre id="code"><font face="courier" size="2" id="code"><br />INSERT INTO #TMP (TestValue) VALUES ('.m-$987879')<br />INSERT INTO #TMP (TestValue) VALUES ('€m-9000')<br />INSERT INTO #TMP (TestValue) VALUES ('lp,.-9')<br />INSERT INTO #TMP (TestValue) VALUES ('mwi-1')<br />INSERT INTO #TMP (TestValue) VALUES ('m.w-12')<br /></font id="code"></pre id="code"><br />ISNUMERIC uses the C++ atof() function, which makes it unreliable at times. <br /><br />But it's interesting to see how slow the two UDF's are in comparison. Actually I've just combined them as I personally never had the need to do such a sorting with data in just one column. But I have used these function for data scrubbing purposes though. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  14. FrankKalis Moderator

    <pre id="code"><font face="courier" size="2" id="code"><br />INSERT INTO #TMP (TestValue) VALUES ('€m-9000')<br /></font id="code"></pre id="code"><br />The first character should be the Euro sign. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  15. Adriaan New Member

    I think we went through the ISNUMERIC problem a few months ago, and never really found an all-encompassing solution (like for E notation and such). Let us assume for now that we are checking on a table that has a nice input mask and no funny characters accepted ...

    quote:You should be able to speed this further up by using a numbers table instead of looping through with WHILE.
    That's more or less what I did for a client when I needed a verification procedure for an alpha coding system they had for a key column. Each position could only have certain values, and specific values at specific positions would narrow the options for other specific positions.

    That's where I came up with the idea of (1) rebuilding the string against a table that holds all valid combinations of (Position, Value) - this you can do in a single SELECT and so it was indeed much faster than looping - and (2) matching the string against "Either" and "Or" criteria for the interdependent positions: a table with a list of "positive" and "negative" LIKE expressions, and one of each set must evaluate as true. If any of the sets did not match the string on either expression, then the string was invalid.
  16. FrankKalis Moderator

    But you need another column to hold the information, right? So, as always theres a trade-off. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  17. Adriaan New Member

    Not sure what column you refer to?

    For the validation stuff I have two auxiliary tables, one with the valid selections for each position, and one with the Either/Or statements.

    The data is entered on a single fixed-width column in my data entry table, validation is handled in an insert/update trigger - but of course also in the client app to avoid nasty messages.
  18. FrankKalis Moderator

    I thought, you were thinking about a table like this


    CREATE TABLE #TMP (TestValue VARCHAR(10), OrderBy VARCHAR(20))


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  19. Adriaan New Member

    Ah - no, that was just my first script on this page!

    You can find my UDF in the post where I compared performance.
  20. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">table that holds all valid combinations <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"> One of my favorite techniques when applicable [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  21. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">table that holds all valid combinations <hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"> One of my favorite techniques when applicable [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">That did sound grotesque, didn't it?[xx(]
  22. FrankKalis Moderator

    I see. Glad I don't need to use this kind of stuff very often. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  23. mmarovic Active Member

    quote:That did sound grotesque, didn't it?
    I'm already used to it. I think I'm going to write an article about a problem that is the most efficiently solved using that technique.
  24. bagman3rd New Member

    As Adrian mentioned, I am almost always dealing with locations with varying length prefixes.
    mw-9-d-050505
    esb-9000
    sb-987879
    mw12
    mw13
    emw-1
    emw-2

    Sometimes they have mw-9-d-050505(mw=monitoring well, d=duplicate, 050505=May 5, 2005), so I need to be able to handle those situation, although the date can be treated as a number, not a date. The script that I posted barfs if the string starts with a number, but I will work on fixing that. All of my databases are relatively small, so speed is usually not much of an issue for me. Thanks a lot for all of the help.

    Archer
  25. Adriaan New Member

    If you want to use Frank's UDFs, make you sure you let dbo.RemoveChars return VARCHAR instead of BIGINT.

    You will run into data that has more 'digits' than even BIGINT can handle.
  26. bagman3rd New Member

    I will make it even trickier for ya'll. These are real values from one of my tables. Neither of the udfs posted seems to handle this data set very well.

    INSERT INTO #TMP (TestValue) VALUES ('B101D-050214')
    INSERT INTO #TMP (TestValue) VALUES ('B102D-050215')
    INSERT INTO #TMP (TestValue) VALUES ('B104D-050216')
    INSERT INTO #TMP (TestValue) VALUES ('B105D-050525')
    INSERT INTO #TMP (TestValue) VALUES ('B201D-050523')
    INSERT INTO #TMP (TestValue) VALUES ('B401D-050216')
    INSERT INTO #TMP (TestValue) VALUES ('B101D-050523D')
    INSERT INTO #TMP (TestValue) VALUES ('B103DR-050213')
    INSERT INTO #TMP (TestValue) VALUES ('B101M-050214')
    INSERT INTO #TMP (TestValue) VALUES ('B102M-050215')

    I will keep working on my script which uses a different approach and tries to break up the string into chunks,

    i.e., for B101D-050214

    chunk1 = B
    chunk2 = 101
    chunk3 = D-
    chunk4 = 050214

    order by chunk1, chunk2, chunk3, chunk4

    Thanks.

    Archer
  27. Adriaan New Member

    With that type of data you can't really establish the order until you have parsed all keys, establishing the longest instance of each chunk, and then stuff each chunk for each key to its maximum length. Finally you may or may not need to send the stuffed key (pardon my French) to that UDF to get the natural ordering.<br /><br />Sounds like you really need to force a stricter format on those key values. Either split the chunks into separate columns, or make each chunk fixed-width with leading zeroes (also on the alpha chunks).<br /><br />Anyway, it's a nice puzzle to solve.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  28. FrankKalis Moderator

    Your real data looks much different from the sample data you've posted. It might have been a good idea if you had posted this data in your original question here, don't you think? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><pre id="code"><font face="courier" size="2" id="code"><br />INSERT INTO #TMP (TestValue) VALUES ('B101D-050523D')<br /></font id="code"></pre id="code"><br />Is this a typo? This might screw up your chunk4.<br /><br />Maybe I've missed this, but I haven't seen you stating that you cannot break this into separated columns. You should really do this instead of parsing each and every chunk to get a correct sorting.<br /><br />And while I agree this is a nice brain-teaser, I also think that a T-SQL solution won't yield good performance at all.<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  29. bagman3rd New Member

    &gt;Your real data looks much different from the sample data you've posted. It might have been a good idea if you had &gt<img src='/community/emoticons/emotion-4.gif' alt=';p' />osted this data in &gt;your original question here, don't you think?<br /><br />Sorry, I guess that I was trying to oversimplify my question.<br /><br />I am an environmental engineer who knows enought about databases to get myself in trouble. And, most of the databases that I work on do not have to meet the rigorous standards that I am sure that ya'll deal with. Most of the time, I am simply using the database to store environmental data for easy and quick retrieval into Excel spreadsheets or to provide a backend for GIS applications. While I could add columns, or preferably build a trigger to do this, it sure would make it a lot easier for my applications and use if there was a function that I could use in an order by. i.e.<br /><br />select * from tblsamples<br />order by natsort(fieldid)<br /><br />I have built a query to parse the first 4 chunks of data, and would be extremely and eternally grateful if someone could show me how to turn this into a function that I could use like above. Thanks a lot. Here is my query:<br /><br />select TOP 100 fieldid as 'sortcolumn',<br />LEFT(fieldid, PATINDEX('%[0-9]%', fieldid) - 1) as 'a',<br /><br />CASE<br />WHENright (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ) not like '%[a-z,-]%'<br />then CAST(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ) AS INT)<br />else<br />cast(<br />left (<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ), <br />case <br />when patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) = 0<br />then patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))<br />else patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -1<br />end<br />) <br />as int)<br />end <br />as 'b', <br /><br />LEFT(<br />right<br />(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />, <br /><br />CASE<br />WHEN PATINDEX('%[0-9]%', <br />right<br />(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />) <br />) = 0<br />THEN <br />LEN(right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />)<br />ELSEPATINDEX('%[0-9]%', <br />right<br />(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />) <br />) -1 <br />END<br />)<br />as 'c',<br /><br />CASE<br />WHENright (<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />, len(<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) - PATINDEX('%[0-9]%', <br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) + 1 ) not like '%[a-z,-]%'<br />then CAST(right (<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />, len(<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) - PATINDEX('%[0-9]%', <br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) + 1 ) AS INT)<br />else<br />cast(<br />left (<br />right (<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />, len(<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) - PATINDEX('%[0-9]%', <br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) + 1 ), <br />case <br />when patindex('%[a-z, -]%', right (<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />, len(<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) - PATINDEX('%[0-9]%', <br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) + 1 )) = 0<br />then patindex('%[a-z, -]%', right (<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />, len(<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) - PATINDEX('%[0-9]%', <br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) + 1 ))<br />else patindex('%[a-z, -]%', right (<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />, len(<br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) - PATINDEX('%[0-9]%', <br />right(<br />right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),<br />len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) - patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) + 1<br />)<br />) + 1 )) -1<br />end<br />) <br />as int)<br />end <br />as 'd'<br /><br />from tblsamplesTEST<br />order by a,b,c,d
  30. FrankKalis Moderator

    Wow, amazing. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Share This Page