asked on

How to get value from String in SQL

Dear user,
i have one column in table Like ,
Row Scheme
------------------------
001 NULL
002 NULL
003 Scheme1-0.50-Point
003 Scheme1-0.50-Point
004 NULL
005 Scheme1-1.00-Point
006 1_N/A

I have scheme I have concat string like :- SchemeName +'-'+ Points +'-'+ Units .

I want O/P using SQL to separate using '-',
Row Scheme Scheme Point Unit
------------------------------------------------
001 NULL NULL 0 NULL
002 NULL NULL 0 NULL
003 Scheme1-0.50-Point Scheme1 0.50 Point
003 Scheme1-0.50-Point Scheme1 0.50 Point
004 NULL NULL 0 NULL
005 Scheme1-1.00-Point Scheme1 1.00 Point
006 1_N/A NULL 0 NULL

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Payal sathavara

ASKER

Yes!!! I got the perfect result...

Sara bhai

You can also try out below query ...

SELECT
      row
      ,scheme
     ,SUBSTRING (scheme , 0 ,CHARINDEX('-',scheme , 0) ) As 'Scheme'
      , STUFF( STUFF(scheme ,1,8,'') ,5,6,'') AS 'Points'
     ,RIGHT (SCHEME , CHARINDEX('-',scheme , CHARINDEX('-',scheme , 0)+1) - CHARINDEX('-',scheme , 0) ) AS 'Unit'
FROM tbl_data ;