Mysql, how to implement and use SQL statements with regular matches the url in the field of digital-CodePudding

The CREATE TABLE ` testvarchar ` (
Unsigned ` id ` int (11) the NOT NULL AUTO_INCREMENT,
` urlId ` int (10) unsigned NOT NULL DEFAULT '0',
` url ` varchar (1000) NOT NULL DEFAULT ',
The PRIMARY KEY (` id `)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Insert into testvarchar (url) values (' https://www.abc.com/xxx/22277598? Preview_for_ml=true '), (' https://www.abced.jp/xxx/13008554 '), (' http://cd.qwer.com/asdf/17996991703.html '), (' https://www.asd.com/zxc/ical/21853688.ics? S=92503680 b6e4692f6a364503048bb964 ');

Now urlId fields need to get the corresponding digital id in the url
22277598130855 4179699703218368 8

How to use SQL to achieve, please?

CodePudding user response:

The SELECT testvarchar. *, REGEXP_SUBSTR (url, '[0-9] +') as n
The FROM testvarchar;
After the statement run n is what you want to capture the Numbers you see changes

CodePudding user response:

No regular expressions in MySQL value function, and based on the data you provide can use the following method:

 
SELECT *, 
SUBSTRING_INDEX (REPLACE (SUBSTRING_INDEX (url, '/', 1), '? ', '. ', '. ', 1) as digital 
The FROM testvarchar

CodePudding user response:

The rules of the above query is take/separation of the last paragraph
For the last paragraph, intercepting again? Before parts, without these two characters, take all the data