Extract a specific value from string (MYSQL)

Question

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?

6 digits + '******' + 4 digits. If '******' is present, then it is a card and I need to retrieve 6 first digits before the stars and 4 digits after — R_life_R
– R_life_R, Commented Oct 17, 2019 at 8:29

P.Salmon · Accepted Answer · 2019-10-17 08:52:43Z

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)

hd1 · Accepted Answer · 2019-10-17 08:32:50Z

0

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.

answered Oct 17, 2019 at 8:32

hd1

34.8k5 gold badges83 silver badges95 bronze badges

Collectives™ on Stack Overflow

Extract a specific value from string (MYSQL)

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related