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
Payal sathavaraWindows Developer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please try full tested solution -

Data Generation -

CREATE TABLE schemes
(	
	 [Row] VARCHAR(10) 
	,[Scheme] VARCHAR(100)
)
GO

INSERT INTO schemes VALUES
('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')
GO

Open in new window


Solution

SELECT Row, CASE WHEN CHARINDEX('-',Scheme,0) > 0 THEN SUBSTRING ( Scheme , 0 ,  CHARINDEX('-',Scheme,0) ) ELSE NULL END Scheme1
		  , ISNULL(CASE WHEN CHARINDEX('-',Scheme,0) > 0 THEN  
				SUBSTRING ( Scheme , CHARINDEX('-',Scheme,0) + 1 ,  CHARINDEX('-',Scheme,CHARINDEX('-',Scheme,0) + 1) - 1 -  CHARINDEX('-',Scheme,0) ) 
			ELSE NULL END,0) Point
		  , CASE WHEN CHARINDEX('-',Scheme,0) > 0 THEN 
					SUBSTRING(Scheme , CHARINDEX('-',Scheme,CHARINDEX('-',Scheme,0) + 1) + 1 , LEN(Scheme) ) ELSE NULL END Unit
FROM schemes

Open in new window


Output

/*------------------------
OUTPUT 
------------------------*/
Row        Scheme1             Point          Unit
---------- ------------------- -------------- -------------
001        NULL                0              NULL
002        NULL                0              NULL
003        Scheme1             0.50           Point
003        Scheme1             0.50           Point
004        NULL                0              NULL
005        Scheme1             1.00           Point
006        NULL                0              NULL

(7 row(s) affected)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Payal sathavaraWindows Developer Author Commented:
Yes!!! I got the perfect result...
0
sarabhaiCommented:
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 ;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.