none
Search for position of character in a variable length string. RRS feed

  • Question

  • Ok.

    Here it goes. I have banged my long enough.

    This seems so simple it probably is. "happens alot to me"

    I have a string/value/row from a column in a table.

    The string looks like this,this is an example resultset.

     

    ID           customervalues

     1            |0|0|0|-1,1,2,4,234,32,453,675,98,26,89|

     2            |123|32|0|1,2,45,14|

     3            |0|32|-1|1,2,36,13|

     

    I need to have a simple loop to grab the comma separated values after the 4th | "pole".

    I dont need those values separated, I only need them as a whole. Not the best design decision but it is how
      it is. This columns values and lengths change all the time but the placement of the data within the column
      and  |'s stays the same as well as the number of |'s.

    These values are used later in the stored procedure on a join using an a.number IN(1,2,45,14)

     

    I tried many ways but SQL kept telling me that I couldnt assign a variable in a SELECT data retrieval process.

    Or the column name becomes invisible inside my loop i think.

    Monday, April 30, 2007 8:38 PM

Answers

  • This should do the task:

     

    Code Snippet


    SET NOCOUNT ON


    DECLARE @MyTable table
       (  ID             int,
          CustomerValues varchar(50)
       )


    INSERT INTO @MyTable VALUES ( 1, '|0|0|0|-1,1,2,4,234,32,453,675,98,26,89|' )
    INSERT INTO @MyTable VALUES ( 2, '|123|32|0|1,2,45,14|' )
    INSERT INTO @MyTable VALUES ( 3, '|0|32|-1|1,2,36,13|' )


    SELECT 
       ID,
       replace( substring( CustomerValues, len( CustomerValues ) - ( charindex( '|', reverse(CustomerValues), 2 ) - 2 ), len( CustomerValues )), '|', '' )
    FROM @MyTable


    ID
    ----------------------------------
    1   -1,1,2,4,234,32,453,675,98,26,89
    2   1,2,45,14
    3   1,2,36,13

     

     

    Monday, April 30, 2007 10:29 PM
    Moderator

All replies

  • As a starting point you could use this to extract the values after the fourth '|'... 

    Code Snippet

    DECLARE @string VARCHAR(50)

    SET @string = '|0|0|0|-1,1,2,4,234,32,453,675,98,26,89|'

    SELECT REPLACE(REVERSE(LEFT(REVERSE(@string), CHARINDEX('|', RIGHT(REVERSE(@string), LEN(@string) -1 )))), '|', '')

     

    You could then use the dbo.Split function, found here:

     

    http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17

     

    ...to populate a temporary table or a table variable onto which you could join in your main query (this join would be performed instead of using IN in the WHERE clause).

     

    Below is a complete example.

     

    Chris

     

    Code Snippet

    USE TempDB

    GO

    CREATE FUNCTION dbo.Split

    (

    @String VARCHAR(200),

    @Delimiter VARCHAR(5)

    )

    RETURNS @SplittedValues TABLE

    (

    OccurenceId SMALLINT IDENTITY(1,1),

    SplitValue VARCHAR(200)

    )

    AS

    BEGIN

    DECLARE @SplitLength INT

     

    WHILE LEN(@String) > 0

    BEGIN

    SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN

    LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END)

     

    INSERT INTO @SplittedValues

    SELECT SUBSTRING(@String,1,@SplitLength)

     

    SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0 THEN ''

    ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)

    END

    RETURN

    END

    GO

     

    DECLARE @string VARCHAR(50)

    DECLARE @splitstring VARCHAR(50)

    SET @string = '|0|0|0|-1,1,2,4,234,32,453,675,98,26,89|'

    SELECT @splitstring = REPLACE(REVERSE(LEFT(REVERSE(@string), CHARINDEX('|', RIGHT(REVERSE(@string), LEN(@string) -1 )))), '|', '')

     

    DECLARE @MyTable TABLE (ID INT, SomeValue VARCHAR(10))

    INSERT INTO @MyTable

    SELECT 1, 'A' UNION

    SELECT 2, 'B' UNION

    SELECT 453, 'C'

     

    SELECT mt.*

    FROM @MyTable mt

         INNER JOIN dbo.Split(@splitstring, ',') s ON CAST(s.SplitValue AS INT) = mt.ID

     

    Monday, April 30, 2007 9:03 PM
  • What you will need to do is write a user-defined function that will return the string that you need.  The way to do is to find the position where the 3rd | starts and the 4th | starts, and return everything between.  The function will be something like this:

    Code Snippet

    create function udf_GetCSVList
    (
        @String varchar(1000)
    )
    returns varchar(750)
    as
    begin
        declare @returnstring varchar(750)

        --//find where 3rd | is
        --//find where 4th | is

        --//use SUBSTRING function to return the difference

        return(@returnstring)
    end


    You can thing wrap this udf around that field in your query:

    select dbo.udf_GetCSVList(fieldname)
    FROM TableName
    Monday, April 30, 2007 9:05 PM
  • If you are always seeking the data between the last set of vertical bars ("poles"), I think you can use

     

    select
       id,
       substring(customervalues, (len(customervalues) - charindex('|',reverse(customervalues),2)), (len(customervalues) - 1))
    from <table>
    order by 1
    ;

     

    Dan
    Monday, April 30, 2007 9:09 PM
  • -- ---------------------------------------------------------------
    --   I used MVP Jens Suessmeyer's Split function to help solve
    --   this problem.  The definition of this function can be found
    --   here:
    --
    --     http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
    --
    --   You might give Erland Sommarskog's writeup here a look also:
    --
    --     http://www.sommarskog.se/arrays-in-sql.html
    -- ---------------------------------------------------------------
    declare @mockup table
       (    id              integer,
            customerValues  varchar(200)
       )
    insert into @mockup
    select 1, '|0|0|0|-1,1,2,4,234,32,453,675,98,26,89|'   union all
    select 2, '|123|32|0|1,2,45,14|' union all
    select 3, '|0|32|-1|1,2,36,13|'

    select t.splitValue
      from @mockup
     cross apply
         ( select j.splitValue
             from dbo.split(customerValues, '|') as j
           where j.OccurenceId = 5
             and id = 1
         ) as s
     cross apply dbo.split(s.splitValue, ',') as t
    where id = 1

    /*
    splitValue
    -------------
    -1
    1
    2
    4
    234
    32
    453
    675
    98
    26
    89
    */

    Monday, April 30, 2007 9:23 PM
    Moderator
  • The REVERSE touch is definitely a good idea if you can take advantage of it.  Also, string lists that are blocked into fixed length segments will tend to run faster than delimited string lists.
    Monday, April 30, 2007 9:25 PM
    Moderator
  • You guys are missing the OP.

     

    I dont need those values separated, I only need them as a whole.

    Monday, April 30, 2007 10:15 PM
    Moderator
  • This should do the task:

     

    Code Snippet


    SET NOCOUNT ON


    DECLARE @MyTable table
       (  ID             int,
          CustomerValues varchar(50)
       )


    INSERT INTO @MyTable VALUES ( 1, '|0|0|0|-1,1,2,4,234,32,453,675,98,26,89|' )
    INSERT INTO @MyTable VALUES ( 2, '|123|32|0|1,2,45,14|' )
    INSERT INTO @MyTable VALUES ( 3, '|0|32|-1|1,2,36,13|' )


    SELECT 
       ID,
       replace( substring( CustomerValues, len( CustomerValues ) - ( charindex( '|', reverse(CustomerValues), 2 ) - 2 ), len( CustomerValues )), '|', '' )
    FROM @MyTable


    ID
    ----------------------------------
    1   -1,1,2,4,234,32,453,675,98,26,89
    2   1,2,45,14
    3   1,2,36,13

     

     

    Monday, April 30, 2007 10:29 PM
    Moderator
  • Thanks to Arnie for the DDL.

     

    SELECT

        ID,

        parsename(replace(substring(CustomerValues, 2, len(CustomerValues) - 2), '|', '.'), 1)

    FROM

        @MyTable

     

     

    AMB

    Monday, April 30, 2007 11:15 PM
    Moderator
  • Very nice.

     

    PARSENAME() certainly works in this situation since there are only 4 parts.

    [ I totally spaced using PARSENAME(). ]

    Monday, April 30, 2007 11:40 PM
    Moderator
  • Yes, very nice -- and sneaky to use PARSENAME for this purpose, tweaking the string to look like the name of a database object!

     

    Dan

    Monday, April 30, 2007 11:51 PM
  • This will work ONLY if the field you want is the last field, and it is followed by a PIPE character (as you show in the example):

     

     

    Code Snippet

    select reverse(substring(reverse(CustomerValues),2,charindex('|',right( reverse(CustomerValues),len(CustomerValues)-1))-1))

    from MyTable

     

     

    Tuesday, May 1, 2007 1:41 AM
  •  

    Arnie Rowland-- Your example worked flawlessly !

     

    Chris Howarth-- I didnt get a chance to try your 2nd snippet/suggestion because I cannot use a function for this task. "Dont  ask".....  

    However the first portion worked flawlessly and is really all I need. I will try it out first thing in the morning.

     

    DanR1--I used your example and had to tweak it a bit with the lengths and such but it put me on the right track.

     

    hunchback-- Yours worked very well for me.I never even thought about the parsename. Sneaky !

     

    Rusag2-Your example worked nicely. Simple and clean. I need to verify that there is no other data past the last pole. I dont believe there is, well I didnt see any with a TOP 10000 so I think we're good.

     

    Yes folks the data I need is always between the last pole and the previous one. For this task anyways. The other pole values are manipulated in VB.. Way easier.. For me anyways.

    Overall i was thinking forward the whole time and never thought to use reverse..  I can now use the values retrieved in my join's a.fieldname IN(@customervalues)  for use in a nested case early on.

     

    I'll update once I try each method out in vb app/stored procedure. You guys are good.

     

     

     

     

    Tuesday, May 1, 2007 5:39 AM
  • Just be aware that you can't use a single variable within an IN condition to check for mulltiple values, which is why I provided the dbo.Split function and a sample query that you could use to replace IN.

     

    i.e. The following two SELECT statements are not logically equivalent:

     

    SELECT...

    WHERE Column IN (1, 2, 3, 4, 5)

     

    DECLARE @Values VARCHAR(9)

    SET @Values = '1, 2, 3, 4, 5'

    SELECT...

    WHERE Column IN (@Values)

     

     

    However, you could dynamically build your SELECT statement, like this:

     

    EXEC ('SELECT ... WHERE Column IN(' + @Values + ')')

     

    Chris

    Tuesday, May 1, 2007 6:51 AM
  •  

    You guys are missing the OP.

     

    I dont need those values separated, I only need them as a whole.

     

    Maybe, but continue reading the original post:

     

    "These values are used later in the stored procedure on a join using an a.number IN(1,2,45,14)"

     

    Obviously a single varchar variable cannot be used for this purpose (unless dynamic SQL is being used, of which there is no mention in the post) and is why further ideas were provided.

     

    Chris

    Tuesday, May 1, 2007 7:06 AM
  • Yes I realized that later on after experimenting with my procedure and things didnt quite work out the way I planned. I finally wound up creating a #temptable with the above code and checking the length of the column value. I've only been using t-sql for 3 months. But I love the one liners !

     

    SELECT

    customers.custid,

    replace(substring(custparameters,len(custparameters)-(charindex('|',reverse(custparameters),2)-2),len(custparameters)),'|','') as 'custids'

    INTO #tempcustlist

    FROM

    customers with(nolock)

     

    where

    employee=@empid

     

     

    CASE

          WHEN custids > 3 THEN

                      'Many Customers'

          ELSE (SELECT TOP 1 custname from #tempcustomers where customer=custids)

    END

     

     

    Monday, May 7, 2007 4:03 PM