Unstring text field?

  • Bob Bridges (10/23/2007)


    I've got a problem that I can't figure out... I'm using the following as suggested to get the data to the right of the delimiter...

    (SELECT STUFF(GL_SWT,1,CHARINDEX('.', @col+'.'),'')

    It works fine when there are 5 characters to the left of the delimiter but when I have 6 characters to the left of the delimiter, the result set includes the delimiter.

    Any ideas?

    Thanks,

    Bob

    Bob, you need to post all the code, I think... you have an extra parenthesis in your code above and idicates that it's part of some other code. When I try this... the code works just fine....

    [font="Courier New"]DECLARE @Col VARCHAR(35)

    SET @Col = '12345.67890'

    SELECT STUFF ( @col, 1, CHARINDEX('.', @Col+'.'), '' )

    SET @Col = '123456.7890'

    SELECT STUFF ( @col, 1, CHARINDEX('.', @Col+'.'), '' )[/font]

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    You got me on the right track... the problem is in the "SET @col" statement. I had

    SET @col = 'Part2.Part1'

    What I didn't realize is that the "Part2" and "Part1" are actually placeholders (literal number of characters). When I made it 123456.12345 it put 6 digits before the delimiter instead of 5 and that fixed it.

    Thanks again, Jeff!

    Bob

  • Just a concept piece I was playing with. Not good for very large data sets in the current form but does a good job as a simple tool

    Inputs are

    @info-2 - which is the data you want back, use a number for a specific position, use 'MAX' or 'MAXIMUM' to get the last position, use 'MIN' or 'MINIMUM' to get the first position (same as using 1), use 'CNT' or 'COUNT' to get the number of positions, use 'EMPTY' to get the count of all positions with no data (such as PART1...PART4 you get a count of 2), and use 'CNTDEL' to get the number of occurrances of the delimiter.

    @delimiter - the delimiter you want to use

    @input - the string you want to parse.

    I am sure there are some enhancements that can be made but this is as far as I went for the time being

    ----------------------------Begin Code---------------------------

    CREATE FUNCTION dbo.GetInfo(@info varchar(7), @delimiter as varchar(5), @input varchar(8000))

    RETURNS VARCHAR(8000)

    AS

    BEGIN

    DECLARE @tbl TABLE (IDX tinyint NOT NULL IDENTITY(1,1), VAL varchar(35) NOT NULL)

    DECLARE @output varchar(8000)

    WHILE CHARINDEX(@delimiter,@input) > 0

    BEGIN

    INSERT @tbl (VAL) VALUES (LEFT(@input,CHARINDEX(@delimiter,@input) - 1))

    SET @input = RIGHT(@input, LEN(@input) - CHARINDEX(@delimiter,@input))

    END

    INSERT @tbl (VAL) VALUES (@input)

    IF ISNUMERIC(@info) = 1

    SET @output = (SELECT VAL FROM @tbl WHERE IDX = CAST(@info as tinyint))

    ELSE

    BEGIN

    IF @info-2 = 'MAX' OR @info-2 = 'MAXIMUM'

    SET @output = (SELECT VAL FROM @tbl WHERE IDX = (SELECT MAX(IDX) I FROM @tbl))

    IF @info-2 = 'MIN' OR @info-2 = 'MINIMUM'

    SET @output = (SELECT VAL FROM @tbl WHERE IDX = (SELECT MIN(IDX) I FROM @tbl))

    IF @info-2 = 'CNT' OR @info-2 = 'COUNT'

    SET @output = (SELECT COUNT(VAL) FROM @tbl)

    IF @info-2 = 'EMPTY'

    SET @output = (SELECT COUNT(VAL) FROM @tbl WHERE LEN(VAL) = 0)

    IF @info-2 = 'CNTDEL' -- Count number of delimiter occurances.

    SET @output = (SELECT COUNT(VAL) - 1 FROM @tbl)

    END

    RETURN(@output)

    END

    GO

  • Sometimes we get data from all kinds of sources that often needs to be converted to a more usable form.

    Parsing functions should be a dime a dozen. I would be surprised if any T-SQL text didn't have at least one to illustrate string functions. However, here is a general purpose one. It will be overkill if you know you will be working with strings that always have only one delimiter (as you show in your example) but you may want something like this for more general use.

    This can be easily modified to a stored proc that returns all the tokens broken up into a result set if that's more what you need.

    /*

    Author:

    Tomm Carr

    Date:

    10/26/2007

    Description:

    Returns the n'th token found in a string. A token is the

    substring that lies between delimiters. For example, with

    a string '111.222.333.444.555.666.777.888.999' and the

    delimiter '.' (period), the tokens are '111', '222', etc.

    The tokens are numbered starting at 1 -- token 1 is '111',

    token 2 is '222', etc.

    NULL is returned if the n'th token was not found (n <= 0 or

    specifies the 10th token in a 9-token string) or if either

    the pattern string or the delimiter character is NULL.

    Examples:

    select dbo.GetToken( '111.222.333.444.555.666.777.888.999', '.', 1 ) -- should return 111

    select dbo.GetToken( '111.222.333.444.555.666.777.888.999', '.', 4 ) -- should return 444

    select dbo.GetToken( '111.222.333.444.555.666.777.888.999', '.', 9 ) -- should return 999

    select dbo.GetToken( '111.222.333.444.555.666.777.888.999', '.', 10 ) -- should return NULL

    select dbo.GetToken( '111.222.333.444.555.666.777.888.999', 'X', 1 ) -- should return the

    entire string.

    */

    create function GetToken (

    @Pattern varchar(8000),

    @Delim char(1),

    @Ind int

    )

    returns varchar(8000)

    as

    begin

    declare @Token varchar(8000),

    @Pos int,

    @Pos1 int,

    @Index int;

    if @Ind > 0

    begin

    select @Index = 1,

    @Pos1 = 0;

    set @Pos = CharIndex( @Delim, @Pattern );

    while @Pos > 0 and @Index < @Ind

    begin

    set @Pos1 = @Pos + 1;

    select @Pos = CharIndex( @Delim, @Pattern, @Pos1 ),

    @Index = @Index + 1;

    end--while

    if @Pos = 0 and @Index = @Ind

    set @Pos = Len( @Pattern ) + 1;

    if @Pos > 0

    set @Token = SubString( @Pattern, @Pos1, @Pos - @Pos1 );

    end--if

    return @Token;

    end--function

    I put this through only a few cursory tests so if you find any flaws, let me know.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • Bob Bridges (10/24/2007)


    Jeff,

    You got me on the right track... the problem is in the "SET @col" statement. I had

    SET @col = 'Part2.Part1'

    What I didn't realize is that the "Part2" and "Part1" are actually placeholders (literal number of characters). When I made it 123456.12345 it put 6 digits before the delimiter instead of 5 and that fixed it.

    Thanks again, Jeff!

    Bob

    My pleasure, Bob... thank you for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply