Results 1 to 8 of 8
  1. #1
    Join Date
    May 2010
    Posts
    7

    Unanswered: Parsing Alphanumeric fields to prefix,suffix,numeric

    Good morning,

    I am having a problem with the code below. I am trying to parse alphanumeric values into alpha prefix, numeric code, alpha suffix.
    Code:
    DECLARE @counter int, 
    @cur_low_pfx varchar(5), @cur_low varchar(10), @cur_low_sfx varchar(5),
    @testing varchar(10)
    
    SET @counter = 0; SET @cur_low='XX12012X'; SET @testing = @cur_low;
    
    --Get Prefix
    WHILE IsNumeric(LEFT(@cur_low,1))<>1
    	BEGIN					
    	SET @counter=@counter+1;
    	SET @cur_low_pfx = LEFT(@testing,@counter);
    	SET @cur_low = SUBSTRING(@testing,@counter+1,LEN(@testing)-@counter);
    	END
    
    --Get Suffix
    SET @counter = 0;
    WHILE IsNumeric(RIGHT(@cur_low,1))<>1
    	BEGIN
    	SET @counter=@counter+1;
    	SET @cur_low_sfx = RIGHT(@testing,@counter);
    	SET @cur_low = SUBSTRING(@cur_low,1,LEN(@testing)-@counter);
    	END
    SELECT @cur_low_pfx as CUR_LOW_PFX, @cur_low as CUR_LOW, @cur_low_sfx AS CUR_LOW_SFX
    The two separate WHILE codes work when used by themselves, but when I combine the two portions together like in the example above, I get the following:
    Code:
    CUR_LOW_PFX	CUR_LOW		CUR_LOW_SFX
    XX		12012		12X
    My desired result is the following:
    Code:
    CUR_LOW_PFX	CUR_LOW		CUR_LOW_SFX
    XX		12012		X
    I can't seem to figure this out. Please assist. Thanks.

  2. #2
    Join Date
    May 2010
    Posts
    7
    Nevermind. I got this working. I just defined @testing again between the two codes.

    Code:
    SET @testing = @cur_low;
    My only complaint is that the code is too lengthy. Does anyone know of a better way to do what I have done above?

    Thanks.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT  prefix      = SUBSTRING(@cur_low_ORIG, 1, end_of_pref)
          , mid         = SUBSTRING(@cur_low_ORIG, end_of_pref + 1, end_of_mid - end_of_pref - 1)
          , suffix      = SUBSTRING(@cur_low_ORIG, end_of_mid, LEN(@cur_low_ORIG))
    FROM    --More derived data:
            (
                SELECT  end_of_pref
                      , end_of_mid      = end_of_pref + PATINDEX('&#37;[^0-9]%', SUBSTRING(@cur_low_ORIG, end_of_pref + 1, LEN(@cur_low_ORIG)))
                FROM    --derived data:
                        (
                            SELECT  end_of_pref     = PATINDEX('%[0-9]%', @cur_low_ORIG) - 1
                        ) AS der_t
            ) AS der_t

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The reason I have done this is because of a few things:
    1)
    Code:
    SELECT  ISNUMERIC('$')
    2) Set based > loops (almost always)

    Also,
    Code:
    SELECT @x = 1, @y = 2, @z = @x + @y
    is more efficient than lots of SETs. One SET is faster than a SELECT, SELECT is faster than two or more SETs.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You could also do it this way, but this much string manipulation is almost calling for a CLR procedure. Much testing would have to be done to make sure which way is the best:

    Code:
    create table #test
    (col1 int,
     col2 varchar(10))
    -- TEST DATA
    insert into #test values (1, 'xx1202xx')
    insert into #test values (2, 'x1202xx')
    insert into #test values (3, 'xx1202x')
    insert into #test values (4, 'xx1202')
    insert into #test values (5, 'xx12022x')
    insert into #test values (6, '12022x')
    -- END TEST DATA
    
    select col1, 
    substring (col2, 1, patindex('&#37;[0-9]%', col2) - 1) as prefix,
    substring (col2, patindex('%[0-9]%', col2), 	len(col2) - patindex('%[0-9]%', col2) - patindex('%[0-9]%', reverse(col2))+2) as middle,
    reverse(substring (reverse(col2), 1, patindex('%[0-9]%', reverse(col2)) - 1)) as suffix
    from #test

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Note also that MCrowley and my solutions could be put in to inline table functions. Yours would have to be a scalar function. I assume you know that the former are more efficient than the latter, and that for the testing the three solutions as MCrowley suggests you would need to use something other than execution plans to identify the most efficient.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    All true, SQL Server is not noted for its speed at string manipulation. Profiler is good at exposing the actual impact of a statement, but hunting down the individual functions involved would be a trick in some cases. Especially where multiple similar functions are used in a single query.

  8. #8
    Join Date
    May 2010
    Posts
    7
    Thanks for both your assistance. It was very informative and I will make note of the suggestions made here.

Posting Permissions

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