none
Substring in query RRS feed

  • Question

  • Hi ALL

     

    I have below data in a table

     

    7102-UNA
    7330-01-110
    1824-01-110
    7102-UNA
    7330-01-110
    1824-01-110
    2000-AT0732
    2150-AT0732
    7102-UNA
    7330-01-110
    824-01-110

    2150-3Q0651

    7340-01-110

     

    From this i need to make another column with the value just after first '-'.

    for eg from if the row is like                     Need to display as      

     
    7340-01-110                                             01-110
    7102-UNA                                                 UNA
    2150-AT0732                                             AT0732
    1234                                                     NULL
    3658-Abc                                              Abc


    Thanks
    Wednesday, June 30, 2010 9:25 PM

Answers

  • Here is one way

    DECLARE @t TABLE(Value VARCHAR(MAX))
    INSERT INTO @t
    SELECT '7340-01-110' UNION 
    SELECT '7102-UNA' UNION 
    SELECT '2150-AT0732' UNION 
    SELECT '1234' UNION 
    SELECT '3658-Abc'
    
    SELECT CASE 
    		WHEN PATINDEX('%-%',Value) > 0
    		THEN SUBSTRING(Value,PATINDEX('%-%',Value) + 1,LEN(Value)) 
    	END
    FROM @t

    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, June 30, 2010 9:38 PM
  • try this -

    DECLARE @temp TABLE

    (

     col1 VARCHAR(100)

    )

    INSERT INTO @temp

    SELECT '7102-UNA'

    UNION ALL

    SELECT '7330-01-110'

    UNION ALL

    SELECT '1824-01-110'

    UNION ALL

    SELECT '7102-UNA'

    UNION ALL

    SELECT '7330-01-110'

    UNION ALL

    SELECT '1824-01-110'

    UNION ALL

    SELECT '2000-AT0732'

    UNION ALL

    SELECT '2150-AT0732'

    UNION ALL

    SELECT '7102-UNA'

    UNION ALL

    SELECT '7330-01-110'

    UNION ALL

    SELECT '824-01-110'

    UNION ALL

    SELECT '2150-3Q0651'

    UNION ALL

    SELECT '7340-01-110'

    UNION ALL

    SELECT 'ABCD'

     

     

    SELECT col1,CASE WHEN CHARINDEX ('-',col1) > 0 THEN SUBSTRING(col1,CHARINDEX('-',col1) + 1,LEN(col1) - CHARINDEX('-',col1) + 1) ELSE NULL END AS [col2]

    FROM @temp

    Thanks,


    Kapil Khalas
    Thursday, July 1, 2010 5:45 AM

All replies

  • What seems to be the exact problem? Take a look at SUBSTRING and CHARINDEX (PATINDEX) functions in Help. See also last reply by Abdallah in this thread http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d996c775-b650-4d63-ae8b-4a143f7e3e4d for a way to solve this problem.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, June 30, 2010 9:30 PM
    Moderator
  • Here is one way

    DECLARE @t TABLE(Value VARCHAR(MAX))
    INSERT INTO @t
    SELECT '7340-01-110' UNION 
    SELECT '7102-UNA' UNION 
    SELECT '2150-AT0732' UNION 
    SELECT '1234' UNION 
    SELECT '3658-Abc'
    
    SELECT CASE 
    		WHEN PATINDEX('%-%',Value) > 0
    		THEN SUBSTRING(Value,PATINDEX('%-%',Value) + 1,LEN(Value)) 
    	END
    FROM @t

    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, June 30, 2010 9:38 PM
  • try this -

    DECLARE @temp TABLE

    (

     col1 VARCHAR(100)

    )

    INSERT INTO @temp

    SELECT '7102-UNA'

    UNION ALL

    SELECT '7330-01-110'

    UNION ALL

    SELECT '1824-01-110'

    UNION ALL

    SELECT '7102-UNA'

    UNION ALL

    SELECT '7330-01-110'

    UNION ALL

    SELECT '1824-01-110'

    UNION ALL

    SELECT '2000-AT0732'

    UNION ALL

    SELECT '2150-AT0732'

    UNION ALL

    SELECT '7102-UNA'

    UNION ALL

    SELECT '7330-01-110'

    UNION ALL

    SELECT '824-01-110'

    UNION ALL

    SELECT '2150-3Q0651'

    UNION ALL

    SELECT '7340-01-110'

    UNION ALL

    SELECT 'ABCD'

     

     

    SELECT col1,CASE WHEN CHARINDEX ('-',col1) > 0 THEN SUBSTRING(col1,CHARINDEX('-',col1) + 1,LEN(col1) - CHARINDEX('-',col1) + 1) ELSE NULL END AS [col2]

    FROM @temp

    Thanks,


    Kapil Khalas
    Thursday, July 1, 2010 5:45 AM