locked
how to get charindex of second position of ' | ' ? RRS feed

  • Question

  • I have a data like 123456|765553|98 in a column, here i need to get the data 98 means onwards of the second positon of '|'  and there is not constant length between the pipes('|'), that is why i cannot use left() or right () of sql.
    Tuesday, October 23, 2012 7:05 AM

Answers

  • DECLARE @Parameter VARCHAR(100)
    SET @Parameter='123456|765553|98' 
    SELECT RIGHT(@Parameter,CHARINDEX('|', REVERSE(@Parameter))-1)


    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    http://dwhanalytics.wordpress.com/

    • Marked as answer by Anshul15 Tuesday, October 23, 2012 7:48 AM
    Tuesday, October 23, 2012 7:22 AM
  • Hi Anshul15,

    You could use the SUBSTRING and CHARINDEX functions :

    declare @text varchar(64)
    set @text = '123456|765553|98'

    SELECT SUBSTRING(@text,len(@text) - CHARINDEX('|', REVERSE(@text)) + 1 + 1 ,len(@text))

    Cheers

    Régis



    Blog

    • Marked as answer by Anshul15 Tuesday, October 23, 2012 11:48 AM
    Tuesday, October 23, 2012 7:53 AM
  • Hi Anshul15,

    to get the middle string you need something like that

    declare @text varchar(64)
    set @text = '123456|765553|98'
    SELECT SUBSTRING(@text,CHARINDEX('|', @text) + 1,LEN(@text) - CHARINDEX('|', @text) - CHARINDEX('|', REVERSE(@text)) ) 

    Cheers

    Régis



    Blog

    • Marked as answer by Anshul15 Friday, November 30, 2012 9:29 AM
    Tuesday, October 23, 2012 2:10 PM

All replies

  • You can use the FINDSTRING to search for the second pipeline. Something like
    SUBSTRING([yourColumn],
    FINDSTRING([yourColumn], "|", 2),
    LEN([yourColumn]) - FINDSTRING([yourColumn], "|", 2)
    )


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


    Tuesday, October 23, 2012 7:11 AM
  • DECLARE @Parameter VARCHAR(100)
    SET @Parameter='123456|765553|98' 
    SELECT RIGHT(@Parameter,CHARINDEX('|', REVERSE(@Parameter))-1)


    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    http://dwhanalytics.wordpress.com/

    • Marked as answer by Anshul15 Tuesday, October 23, 2012 7:48 AM
    Tuesday, October 23, 2012 7:22 AM
  • If you are using SQL Server 2012, you cal also use the expression as create a new column/replace existing using the Derived Column Task

    TOKEN(Col1,"|",3)


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Tuesday, October 23, 2012 7:26 AM
  • Thank you.
    Tuesday, October 23, 2012 7:49 AM
  • here FindString () is giving error:

    'FINDSTRING' is not a recognized built-in function name.

    I am using SQL Server 2005. Thanks in advance.

    Tuesday, October 23, 2012 7:50 AM
  • Hi Anshul15,

    You could use the SUBSTRING and CHARINDEX functions :

    declare @text varchar(64)
    set @text = '123456|765553|98'

    SELECT SUBSTRING(@text,len(@text) - CHARINDEX('|', REVERSE(@text)) + 1 + 1 ,len(@text))

    Cheers

    Régis



    Blog

    • Marked as answer by Anshul15 Tuesday, October 23, 2012 11:48 AM
    Tuesday, October 23, 2012 7:53 AM
  • here FindString () is giving error:

    'FINDSTRING' is not a recognized built-in function name.

    I am using SQL Server 2005. Thanks in advance.

    It's a SSIS function, not TSQL. You can use it in a derived column.
    http://msdn.microsoft.com/en-us/library/ms141748(v=sql.90).aspx

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Tuesday, October 23, 2012 8:00 AM
  • Hi _AKS,

    If i want to get middle string i.e.765553, then i wrote the query like

    SUBSTRING(@Parameter,Charindex('|', @Parameter)+1,len(@Parameter))

    then i am able to find the value: 765553|98. How to get middle one only?

    Thanks in advance.

    Tuesday, October 23, 2012 11:42 AM
  • It's also working.

    Thank you Regis.

    Tuesday, October 23, 2012 11:48 AM
  • Hi Anshul15,

    to get the middle string you need something like that

    declare @text varchar(64)
    set @text = '123456|765553|98'
    SELECT SUBSTRING(@text,CHARINDEX('|', @text) + 1,LEN(@text) - CHARINDEX('|', @text) - CHARINDEX('|', REVERSE(@text)) ) 

    Cheers

    Régis



    Blog

    • Marked as answer by Anshul15 Friday, November 30, 2012 9:29 AM
    Tuesday, October 23, 2012 2:10 PM