none
String function RRS feed

  • Question

  •   

    I want only certain string from following string. i need to extract only X111,A007&S112 from below example. 

     its not with fix length but it has | before starts. Is there any function to get value.

    Example:

                column name 111 

      'it is a number with code|x111 - Ferrari'
       'Aviation and air force|A007 - Air service '
       'Electronics and Hardware and software|S112 - parts of computer' 

    Looking for:

                column name 111 

         x111

         A007

         S112

    Tuesday, September 10, 2019 5:45 PM

Answers

  • Helo Steve,

    You can also use below script: (it won't throw error for column value ''-empty)

    DECLARE @TAB AS TABLE(INPUT VARCHAR(500)); INSERT @TAB VALUES

    (''), ('it is a number with code|x111 - Ferrari'), ('Aviation and air force|A007 - Air service '), ('Electronics and Hardware and software|S112 - parts of computer'); SELECT INPUT, SUBSTRING( (SUBSTRING( INPUT, CHARINDEX ('|', INPUT)+1, LEN(INPUT) ) ), 0, CHARINDEX (' ',(SUBSTRING( INPUT, CHARINDEX ('|', INPUT)+1, LEN(INPUT) )),0) ) AS EXPECTED_RESULTSET FROM @TAB;

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    • Marked as answer by _Steve121 Tuesday, September 10, 2019 11:35 PM
    Tuesday, September 10, 2019 7:50 PM

All replies

  • Good day Steve,

    Please check if the following solve your needs:

    DECLARE @t VARCHAR(1000) = 'Electronics and Hardware and software|S112 - parts of computer' 
    SELECT SUBSTRING(
    		@t,
    		CHARINDEX ('|', @t)+1,
    		CHARINDEX (' ', @t, CHARINDEX ('|', @t)) - CHARINDEX ('|', @t) - 1
    	)

    You can use the same logic with data in table

    SELECT SUBSTRING(
    		Column_Name,
    		CHARINDEX ('|', Column_Name)+1,
    		CHARINDEX (' ', Column_Name, CHARINDEX ('|', Column_Name)) - CHARINDEX ('|', Column_Name) - 1
    	)
    FROM Table_Name


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Tuesday, September 10, 2019 6:20 PM
    Moderator
  • Helo Steve,

    You can also use below script: (it won't throw error for column value ''-empty)

    DECLARE @TAB AS TABLE(INPUT VARCHAR(500)); INSERT @TAB VALUES

    (''), ('it is a number with code|x111 - Ferrari'), ('Aviation and air force|A007 - Air service '), ('Electronics and Hardware and software|S112 - parts of computer'); SELECT INPUT, SUBSTRING( (SUBSTRING( INPUT, CHARINDEX ('|', INPUT)+1, LEN(INPUT) ) ), 0, CHARINDEX (' ',(SUBSTRING( INPUT, CHARINDEX ('|', INPUT)+1, LEN(INPUT) )),0) ) AS EXPECTED_RESULTSET FROM @TAB;

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    • Marked as answer by _Steve121 Tuesday, September 10, 2019 11:35 PM
    Tuesday, September 10, 2019 7:50 PM
  • select parsename( replace(replace([column name 111],'|','.'),' -','.'), 2)

    from yutable
    Tuesday, September 10, 2019 8:10 PM
    Moderator
  • Hi Ronen,

    The code throws error if the input is blank/empty.


    • Edited by Arulmouzhi Tuesday, September 10, 2019 8:35 PM edited for understanding
    Tuesday, September 10, 2019 8:34 PM
  • Hi Jingyang Li,

    The code changes the input as null if the input is blank/empty. 

    Tuesday, September 10, 2019 8:37 PM
  • Nice catch Arulmouzhi
    +
    5

    Thanks


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, September 11, 2019 2:02 AM
    Moderator