I have an Customer_value column. The column contains values like:

DAL123245,HC.533675,ABC.01232423
HC.3425364,ABC.045367544,DAL4346456
HC.35344,ABC.03543645754,ABC.023534454,DAL.4356433
ABC.043534553,HC.3453643,ABC.05746343

What I am trying to do is get the number after the first "ABC.0" string. For example, this is what I would like to get:

1. 1232423
2. 5367544
3. 3543645754
4. 43534553 this is what I tried: Substring(customer_value,charindex('ABC.', customer_value) 5, len(customer_value)) as dataneeded

The issue that I got is for 1 and 2 I got that right data as needed, but for 3 and 4, because there are multiple ABC so it gave me everything after the first ABC.

How can I get the number after the first ABC. only?

Thank you so much

CodePudding user response：

Just another option is to use a bit of JSON to parse and preserve the sequence in concert with a CROSS APPLY

Note: Use OUTER APPLY to see NULL values

Example

Select NewVal = replace(Value,'ABC.0','') 
 From  YourTable A
 Cross Apply ( 
                Select Top 1 *
                 From  OpenJSON( '["' replace(string_escape(customer_value,'json'),',','","') '"]' )
                 Where Value like 'ABC.0%'
                 Order by [key]
              ) B

Results

NewVal
1232423
45367544
3543645754
43534553

CodePudding user response：

On the assumption you are using SQL Server (given your use of charindex()/substring()/len()) you can use apply to calculate the starting position and then find the next occurence utilising the start position optional parameter of charindex, then get the substring between the values.

select Substring(customer_value, p1.v, Abs(p2.v-p1.v)) as dataneeded
from t
cross apply(values(charindex('ABC.', customer_value) 5))p1(v)
cross apply(values(charindex(',', customer_value,p1.v)))p2(v)