CODEWITHSUNDEEP
sqlsql-server
sneh999
sneh999

Reputation: 65

Issue in Selection with CHARINDEX and SUBSTRING

I have 2 questions here.

  1. I have a column 'Campaign' with these entries. My objective is to extract the Cost part from the string and create a new column Cost in my table

    29693214 - Live -JUTL Phase 2 Creator Stories Trailer  * 7.12 - 7.25 * Video Views * $28,169.01 * BG - Mob
89695072 - Live -WUTL Retargeting JG  * 7.16 - 7.31 * Link Clicks * $23,474.18 * KG - Mob

    I tried select SUBSTRING(Campaign,CHARINDEX('$',Campaign) +1,???) .I am unable to figure out the '???' part. I want to start with the index next to '$' and continue till the '*' symbol to capture the cost.

    While creating a new column I follow these steps

    Alter table T ADD NewColumn varchar(100)

  2. then I do this 

    Update T SET NewColumn = 'Say I want that cost part from the above question here'

    Is there any efficient way to do this in single shot?

Upvotes: 0

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

I think apply makes these operations a bit simpler:

select left(v1.str1, charindex(' ', v1.str1))
from (values ('29693214 - Live -JUTL Phase 2 Creator Stories Trailer * 7.12 - 7.25 * Video Views * $28,169.01 * BG - Mob')) v(str) cross apply
     (values (stuff(str, 1, charindex('$', str), ''))) v1(str1);

This can readily be incorporated into an update:

Update t
    set newcolumn = left(v.str1, charindex(' ', v.str1))
    from t cross apply
         (values (stuff(t.str, 1, charindex('$', str), ''))) v(str1)

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37500

DDL for testing query:

declare @tbl table(Campaign varchar(200));
insert into @tbl values
('29693214 - Live -JUTL Phase 2 Creator Stories Trailer * 7.12 - 7.25 * Video Views * $28,169.01 * BG - Mob'),
('189695072 - Live -WUTL Retargeting JG * 7.16 - 7.31 * Link Clicks * $23,474.18 * KG - Mob');

If the cost is always separated by a asterisk * from other part of the string, you could use below query:

select trim(substring(Campaign, dollarIndex + 1, asteriskIndex - dollarIndex - 1)) from (
    select Campaign, charindex('$', Campaign) dollarIndex,
           charindex('*', Campaign, charindex('$', Campaign)) asteriskIndex
    from @tbl
) a

To use it in update statement, you could use the same query, but we need to transform above into single query:

update @tbl set Campaign = trim(substring(Campaign, charindex('$', Campaign) + 1, charindex('*', Campaign, charindex('$', Campaign)) - charindex('$', Campaign) - 1))

Note: consider if it's worthy keeping data that you already have (redundancy). If it can be always be parsed from your column in the same way you could use view for this task.

On the other hand it might be qiute expensive to query such data and might be bad for performance.

Upvotes: 0

Related Questions