CODEWITHSUNDEEP
mysql
R_life_R
R_life_R

Reputation: 816

Extract a specific value from string (MYSQL)

I have the MISC column in a MYSQL table with the following value:

'PrimeCC_Stripe/XX_582130/PMethod=VISA/CardType=VISA/489930******8888/12/2020/TraceId=7182992'

another example:

'-1/error/PMethod=VISA/CardType=VISA/489930******8888/12/2020/TraceId=714291'

or

'Cancelled by PendingDepositCleanerJob. User didn't finish the payment process properly.'

Im am trying to extract the CARD number as another column in my query, here it should be: '489930******8888' or nothing if no card number is included in the MISC column. What is the best option to extract this information?

Upvotes: 0

Views: 244

Answers (2)

P.Salmon
P.Salmon

Reputation: 17665

A bit of string manipulation

drop table if exists t;
create table t (str varchar(100));
insert into t values
('PrimeCC_Stripe/XX_582130/PMethod=VISA/CardType=VISA/489930******8888/12/2020/TraceId=7182992'),
('Cancelled by PendingDepositCleanerJob. User didnt finish the payment process properly.'),
('123456******7891')
;

select str, 
        case when instr(str,'******') > 0 then
              concat(
              substring(str, instr(str,'******') - 6, 6),
              '******',
              substring(str, instr(str,'******') + 6, 4)
              )
        end
from t;

+----------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PrimeCC_Stripe/XX_582130/PMethod=VISA/CardType=VISA/489930******8888/12/2020/TraceId=7182992 | 489930******8888                                                                                                                                                |
| Cancelled by PendingDepositCleanerJob. User didnt finish the payment process properly.       | NULL                                                                                                                                                            |
| 123456******7891                                                                             | 123456******7891                                                                                                                                                |
+----------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

But it won't work if you have more than 1 occurrance of ****** or the number format differs (or is only a partial)

Upvotes: 1

hd1
hd1

Reputation: 34677

MySQL supports regular expressions, which we can use as a last resort.

SELECT REGEXP_SUBSTR(misc, '489930******8888') as CARD

The default value returned will be null. Hope this sorts you.

Upvotes: 0

Related Questions