CODEWITHSUNDEEP
sqlsql-servert-sql
YenRuby
YenRuby

Reputation: 51

Multiple replace for the String in a column

I have a column present which is as shown below

Column1
-------------------------------
{Tablets/Tonic} Benadryl 25 mcg IV
{Tablets/Tonic} Coughx 50 ml IV
{Tablets/Tonic} Nucel 1 mg IV
{Tablets/Tonic} ColdYmx 2 mg IV
etc.,

How we can achieve the desired output as two separate columns which is expected (shown below)

Column1                  column2 
----------------------   ---------
Benadryl                 25 mcg IV 
Coughx                   50 ml IV
Nucel                    1 mg IV
ColdYmx                  2 mg IV
etc.,

Upvotes: 0

Views: 69

Answers (1)

Dale K
Dale K

Reputation: 27202

Just continue what you started a second time and substring the results from the first substring. (cross apply is just a fancy way to do a sub-query).

declare @MyTable table (Transcript varchar(64));

insert into @MyTable (Transcript)
values
('{Tablets/Tonic} Benadryl 25 mcg IV'),
('{Tablets/Tonic} Coughx 50 ml IV'),
('{Tablets/Tonic} Nucel 1 mg IV'),
('{Tablets/Tonic} ColdYmx 2 mg IV');

select
    substring(ActualName, 0, charindex(' ',ActualName)) Column1
    , substring(ActualName, charindex(' ',ActualName)+1, len(ActualName)) Column2
from @MyTable
cross apply (
    values (substring(Transcript, charindex(' ',Transcript)+1, len(Transcript)))
) x (ActualName);

Returns:

Column1 Column2
Benadryl 25 mcg IV
Coughx 50 ml IV
Nucel 1 mg IV
ColdYmx 2 mg IV

Note: Your query as shown doesn't actually work - you don't want to start from 15 the start is from the first detected space.

Upvotes: 2

Related Questions