Results 1 to 10 of 10

Thread: using substring

  1. #1
    Join Date
    Jan 2004
    Posts
    53

    Unanswered: using substring

    Hi

    Can anyone show me how to do the following:

    I have this snippet of data

    ids
    -----------------------------------
    1582270|1582277
    1582270|1582277|1582286
    1582270

    I want to return the id (the id may not always have the same number of numbers) after the last pipe (|) delimter.

    So i want a rs like this :
    ids
    -----------------------------------
    1582277
    1582286
    1582270


    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    declare @v as varchar(30)
    , @r as varchar(30)
    , @p as integer

    set @v = '1582270|1582277|1582286'
    set @v = '1582270'
    set @p = (select charindex('|', reverse(@v)))
    if @p = 0 set @r = @v
    if @p > 0 set @r = (select substring(@v, len(@v) - @p + 2, len(@v)))
    select @r

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This sounds an awful lot like homework, but I'd use:
    Code:
    SELECT Right(ids, Charindex('|', Reverse(ids)) - 1) AS id
       FROM myTable
    -PatP

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's your major?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Posts
    53
    this isn't homework

    when i use
    select cast(right(ids, charindex('|', reverse(ids)) - 1) FROM tbltest

    I get the error message

    invalid length parameter passed to the substring function.

  6. #6
    Join Date
    Jan 2004
    Posts
    53
    it's alright i've done it like this

    select
    case
    when charindex('|',ids) = 0
    then ids
    else right(ids, charindex('|', reverse(ids))-1)
    end
    as ids
    from tbltest

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, so it's not homework...could you explain the value of doing this?

    Don't the other id's have any meaning or worth?

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(ids varchar(8000))
    
    INSERT INTO myTable99(ids)
    SELECT '1582270|1582277' UNION ALL
    SELECT '1582270|1582277|1582286' UNION ALL
    SELECT '1582270'
    GO
    
    SELECT Right(ids, CASE 
    		  WHEN (Charindex('|', Reverse(ids))) = 0 
    		  THEN  LEN(ids)
    		  ELSE (Charindex('|', Reverse(ids)) - 1)
    		  END) AS id
       FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    could you explain the value of doing this?
    oh ye of little imagination...

    it's a pipe-delimited list of ids

    a Zero Normal Form design

    assume new ids are concatenated to the end

    what's the sql to pull out the latest id?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would add them to the begining...not the end....

    I'd still like to hear their response though....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    I would add them to the begining...not the end....
    No, you'd never get to a 0NF schema. I don't think that the thought would even occur to you.

    -PatP

Posting Permissions

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