Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2009
    Location
    in the web
    Posts
    13

    Unanswered: Sort an alphanumeric field

    Hi,
    I need to sort a field that contains a complex structure, some data like :
    E3.2a, E3.2b,A5.2, A1.1, A4.1, A5.10, A4.9 E.1.5b, F.15c...
    I'v tried something like :
    Code:
    SELECT REF_UTIL,
    substring (REF_UTIL,PATINDEX('%[A-Z,a-z]%',REF_UTIL),PATINDEX('%[0-9]%',REF_UTIL)-1 )
    , substring(REF_UTIL,PATINDEX('%[0-9]%',REF_UTIL),PATINDEX('%[A-Z,a-z,0-9].%',REF_UTIL))
    , substring(REF_UTIL,PATINDEX('%.[0-9]%',REF_UTIL)+1,1000)
     FROM DEP_ETAT_BRD_PRIX_TMP655
    ORDER BY 
    substring (REF_UTIL,PATINDEX('%[A-Z,a-z]%',REF_UTIL),PATINDEX('%[0-9]%',REF_UTIL)-1 )
    , substring(REF_UTIL,PATINDEX('%[0-9]%',REF_UTIL),PATINDEX('%[A-Z,a-z,0-9].%',REF_UTIL))
    , substring(REF_UTIL,PATINDEX('%.[0-9]%',REF_UTIL)+1,1000)
    it seems to be impossible to parse this in sql
    any suggestions please ?

    thanks
    we like difficulties

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    witch, can you give an example of what order you want the rows in? And what version of SQL Server are you using?

  3. #3
    Join Date
    Apr 2009
    Location
    in the web
    Posts
    13
    I'm using sql sever 2000
    I need a logic sort
    i have data like :
    A5.2, A1.1, A4.1,A5.2A, A5.10A, A4.9A
    with a simple order by, i get :
    A1.1, A4.1, A4.9A, A5.2, A5.10A, A5.2A
    as you see, the correct sort should be :
    A1.1, A4.1, A4.9A, A5.2, A5.2A, A5.10A
    in short, when the number exceed 9, it's not well sorted
    the field's type is obviously character.
    we like difficulties

  4. #4
    Join Date
    Apr 2007
    Posts
    183
    Code:
    DECLARE	@Sample TABLE
    	(
    		Data VARCHAR(20)
    	)
    
    INSERT	@Sample
    SELECT	'E3.2a' UNION ALL
    SELECT	'E3.2b' UNION ALL
    SELECT	'A5.2' UNION ALL
    SELECT	'A1.1' UNION ALL
    SELECT	'A4.1' UNION ALL
    SELECT	'A5.10' UNION ALL
    SELECT	'A4.9' UNION ALL
    SELECT	'E.1.5b' UNION ALL
    SELECT	'F.15c'
    
    SELECT		*
    FROM		@Sample
    ORDER BY	LEN(PARSENAME('.' + Data, 3)),
    		PARSENAME('.' + Data, 3),
    		LEN(PARSENAME('.' + Data, 2)),
    		PARSENAME('.' + Data, 2),
    		LEN(PARSENAME('.' + Data, 1)),
    		PARSENAME('.' + Data, 1)
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  5. #5
    Join Date
    Apr 2009
    Location
    in the web
    Posts
    13
    Hello Peso,
    thanks for replying, this helps me a lot
    I've done some modifications, because the structure can be formed with only two parts, or sometimes with more than two, so
    Code:
    DECLARE	@Sample TABLE
    	(
    		Data VARCHAR(20)
    	)
    
    INSERT	@Sample select code from T1
      
    
    SELECT		*
    ,
    
    CASE
                WHEN LEN(PARSENAME('.' + Data, 3)) is null 
                    THEN LEN(PARSENAME('.' + Data,2))                 
                ELSE 
                    LEN(PARSENAME('.' + Data, 3)) 
            END --AS part 
    , 
    LEN(PARSENAME('.' + Data, 3)) as A ,
    		PARSENAME('.' + Data, 3) as B,
    		LEN(PARSENAME('.' + Data, 2)) as C,
    		PARSENAME('.' + Data, 2) as D,
    		LEN(PARSENAME('.' + Data, 1)) as E,
    		PARSENAME('.' + Data, 1)   as F
    FROM		@Sample
    
    ORDER BY	
        CASE
                WHEN LEN(PARSENAME('.' + Data, 3)) is null 
                    THEN LEN(PARSENAME('.' + Data,2))             
                ELSE 
                    LEN(PARSENAME('.' + Data, 3)) 
            END --AS part 
    ,
    
    		PARSENAME('.' + Data, 3),
    		LEN(PARSENAME('.' + Data, 2)),
    		PARSENAME('.' + Data, 2),
    		LEN(PARSENAME('.' + Data, 1)),
    		PARSENAME('.' + Data, 1)
    it's very nice
    well, it seems to be resolved
    but i can come back for more questions

    thanks
    we like difficulties

  6. #6
    Join Date
    Apr 2009
    Location
    in the web
    Posts
    13
    I still have a problem, I've found that data like
    A.1,A.3,A.2,A.1a, A.1a, A.3, B...
    aren't well sorted
    i have as a result
    A.1,A.2,A.3,A.1a, B..
    the code like "A.1a" should follow the code "A.1" before the "A.2"
    is this possible?

    I've tryed things like :
    [CODE]
    DECLARE @TEMP TABLE (

    Code:
     [nvarchar](50) COLLATE French_CI_AS NULL
    	
    ) 
    
    INSERT    @TEMP 
    
    select code from T1 ---ici la requete de selection
     
    
    SELECT  *, 
    CASE when PARSENAME('.' + code, 1) NOT LIKE '%[A-Za-z]' THEN
      PARSENAME('.' + code, 1) ELSE  
      replace (PARSENAME('.' + code, 1) , 
            substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000),
             '') 
        END AS TEST, 
    
    CASE when PARSENAME('.' + code, 1) LIKE '%[A-Za-z]' THEN 
      replace (PARSENAME('.' + code, 1) , 
            substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000),
             '') 
     ELSE LEN(PARSENAME('.' + code, 1))  END as TRI5
            ,
    CASE when PARSENAME('.' + code, 1) LIKE '%[A-Za-z]' THEN 
       substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000)
     ELSE PARSENAME('.' + code, 1) END AS TRI6
     
    FROM        @TEMP
    
    ORDER BY
    
     CASE
                WHEN PARSENAME('.' + code, 3)is null THEN PARSENAME('.' + code,2 )                        
                 ELSE  PARSENAME('.' + code, 3)
                END --AS part
            ,
     CASE
                WHEN LEN(PARSENAME('.' + code, 3)) is null THEN LEN(PARSENAME('.' + code,2))            
                 ELSE LEN(PARSENAME('.' + code, 3))
                END --AS part
            ,
                 
            PARSENAME('.' + code, 3),
            LEN(PARSENAME('.' + code, 2)),
            PARSENAME('.' + code, 2),
    
          --------TEST
    CASE when PARSENAME('.' + code, 1) NOT LIKE '%[A-Za-z]%' THEN
      len(PARSENAME('.' + code, 1)) ELSE 
      replace (PARSENAME('.' + code, 1) , 
            substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000),
             '')  END , 
    CASE when PARSENAME('.' + code, 1) NOT LIKE '%[A-Za-z]%' THEN
      PARSENAME('.' + code, 1) ELSE 
      replace (PARSENAME('.' + code, 1) , 
            substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000),
             '')  END , 
    
    CASE when PARSENAME('.' + code, 1) LIKE '%[A-Za-z]' THEN 
       substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000)
     ELSE PARSENAME('.' + code, 1) END
    we like difficulties

  7. #7
    Join Date
    Apr 2009
    Location
    in the web
    Posts
    13
    I think this is more logical : [CODE]
    DECLARE @TEMP TABLE (

    Code:
     [nvarchar](50) COLLATE French_CI_AS NULL
    	
    ) 
    
    INSERT    @TEMP 
    
    select code from T1 ---ici la requete de selection
     
    
    SELECT  *, 
    
          --------TEST
    CASE when PARSENAME('.' + code, 1) NOT LIKE '%[A-Za-z]%' THEN
      PARSENAME('.' + code, 1)   ELSE 
    CAST(replace (PARSENAME('.' + code, 1) , 
            substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000),
             '')  AS NUMERIC ) END  AS REF1,
    
    CASE when PARSENAME('.' + code, 1) NOT LIKE '%[A-Za-z]%' THEN
     len(PARSENAME('.' + code, 1))  ELSE 
    replace (PARSENAME('.' + code, 1) , 
            substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000),
             '') END AS REF2 , 
    
    CASE when PARSENAME('.' + code, 1) LIKE '%[A-Za-z]' THEN 
     cast(replace (PARSENAME('.' + code, 1) , 
            substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000),
             '') AS numeric )  END AS REF3, 
    CASE when PARSENAME('.' + code, 1) LIKE '%[A-Za-z]' THEN 
     substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000)
     ELSE PARSENAME('.' + code, 1) END  REF4
     
    FROM        @TEMP
    
    ORDER BY
    
     CASE
                WHEN PARSENAME('.' + code, 3)is null THEN PARSENAME('.' + code,2 )                        
                 ELSE  PARSENAME('.' + code, 3)
                END --AS part
            ,
     CASE
                WHEN LEN(PARSENAME('.' + code, 3)) is null THEN LEN(PARSENAME('.' + code,2))            
                 ELSE LEN(PARSENAME('.' + code, 3))
                END --AS part
            ,
                 
            PARSENAME('.' + code, 3),
            LEN(PARSENAME('.' + code, 2)),
            PARSENAME('.' + code, 2),
    
          --------TEST
    CASE when PARSENAME('.' + code, 1) NOT LIKE '%[A-Za-z]%' THEN
      PARSENAME('.' + code, 1)   ELSE 
    CAST(replace (PARSENAME('.' + code, 1) , 
            substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000),
             '')  AS NUMERIC ) END ,
    
    CASE when PARSENAME('.' + code, 1) NOT LIKE '%[A-Za-z]%' THEN
     len(PARSENAME('.' + code, 1)) ELSE  
      replace (PARSENAME('.' + code, 1) , 
            substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000),
             '') 
        END , 
    
    CASE when PARSENAME('.' + code, 1) LIKE '%[A-Za-z]' THEN 
     cast(replace (PARSENAME('.' + code, 1) , 
            substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000),
             '') AS numeric )  END, 
    CASE when PARSENAME('.' + code, 1) LIKE '%[A-Za-z]' THEN 
     substring(PARSENAME('.' + code, 1),PATINDEX('%[A-Z,a-z]%',PARSENAME('.' + code, 1)),1000)
     ELSE PARSENAME('.' + code, 1) END  
    
    
    ----------FIN
    but i would like a solution more simple than that...

    thanks
    we like difficulties

  8. #8
    Join Date
    Apr 2007
    Posts
    183
    Here is one way

    Code:
    DECLARE	@Sample TABLE
    	(
    		Data VARCHAR(20)
    	)
    
    INSERT	@Sample
    SELECT	'A.1' UNION ALL
    SELECT	'A.10' UNION ALL
    SELECT	'A.2' UNION ALL
    SELECT	'A.3' UNION ALL
    SELECT	'A.1a'
    
    SELECT		Data
    FROM		(
    			SELECT	Data,
    				REPLACE(REPLACE(Data, 'A.', '10.'), 'A', '.10') AS x
    			FROM	@Sample
    		) AS d
    ORDER BY	CAST('/' + x + '/' AS HIERARCHYID)
    Microsoft SQL Server MVP

    N 5604'39.26"
    E 1255'05.63"

  9. #9
    Join Date
    Apr 2009
    Location
    in the web
    Posts
    13
    Hi Peso,
    I'm working with the version 2005 of sql server, the key word HIERARCHYID isn't recognized...

    it's really anything these codes... i've found that was more complicated than i've imagined, i have some codes like :
    A1.1, A1.1b, E1.1.1, E1.1.3b, F.1a, F.16b, F.14.1 ....
    and i can only sort when it's like A1.1, A.1 or B.1

    we like difficulties

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •