select only digits, numbers from text

Question

I have a field text, In it there is information about such

sch hcbhsc hscbshcbc  xxxxxxxx sgxfag jdhajdh;

dchbdbc bdcbdh bchdbd  xx/xx-xxxx/xx svdhs sbjbsc

bdchbdc jncjdnc jbcjb  xx/xx-xxxxx/xx gcvsgc jcbjsb

dchjbd bhjcbdcb bdcbcd  xx-xxxx/xx shchscv hscbhsc

dhcbhd jdcbjdb jdcnjdcn  xx-xxxxx/xx shcvsch jbscjc

Place x is only a digit, I need to write select and only those numbers are taken

score 2 · Answer 1 · edited May 23 '17 at 12:25

2

Use SUBSTRING and PATINDEX string functions IN SQL server :

   SELECT SUBSTRING(Your_FieldName, PATINDEX('%[0-9]%', Your_FieldName),
   LEN(Your_FieldName))

For MYSQL refer below URL :

Query to get only numbers from a string string

edited May 23 '17 at 12:25

Community

1
1

answered Apr 04 '17 at 06:02

Mansoor

4,061
1
17
27

2

FUNCTION databasename.PATINDEX does not exist – Tigran Shahnazaryan Apr 04 '17 at 06:06

score 0 · Answer 2 · answered Apr 04 '17 at 11:12

There is no formal PATINDEX() function in MySQL that achieves both the regex pattern lookup with returned character index, define User-Defined function that loops through each character in the length of a string and checks a REGEXP pattern on the character. Once created, use such a function in-line of a query.

DROP FUNCTION IF EXISTS PatIndex;

DELIMITER $$

CREATE FUNCTION PatIndex(pattern VARCHAR(255), tblString VARCHAR(255)) RETURNS INTEGER
DETERMINISTIC
BEGIN

DECLARE i INTEGER;
SET i = 1;

myloop: WHILE (i <= LENGTH(tblString)) DO

    IF SUBSTRING(tblString, i, 1) REGEXP pattern THEN
        RETURN(i);
        LEAVE myloop;        
    END IF;    

    SET i = i + 1;

END WHILE; 

RETURN(0);

END

FUNCTION databasename.LEN does not exist – Tigran Shahnazaryan Apr 04 '17 at 11:30 — Tigran Shahnazaryan, Apr 04 '17 at 11:30

Dimitar Darazhanski · Answer 3 · 2019-01-14T21:01:01.547

Here is a MySQL function (routine) that will do just that. It is an improved version from the solution given here: how-to-get-only-digits-from-string-in-mysql

This improved version can handle much larger numbers. The old solution was limited by the INTEGER value, so if you had phone numbers for example (or string containing many digits), it would fail with out of range for column.

DELIMITER $$ 
CREATE FUNCTION ExtractNumber (in_string VARCHAR(50)) 
    RETURNS varchar(50)
    NO SQL
BEGIN
    DECLARE ctrNumber VARCHAR(50);
    DECLARE finNumber VARCHAR(50) DEFAULT '';
    DECLARE sChar VARCHAR(1);
    DECLARE inti VARCHAR(50) DEFAULT 1;

    IF LENGTH(in_string) > 0 THEN
        WHILE(inti <= LENGTH(in_string)) DO
            SET sChar = SUBSTRING(in_string, inti, 1);
            SET ctrNumber = FIND_IN_SET(sChar, '0,1,2,3,4,5,6,7,8,9'); 
            IF ctrNumber > 0 THEN
                SET finNumber = CONCAT(finNumber, sChar);
            END IF;
            SET inti = inti + 1;
        END WHILE;
        RETURN CAST(finNumber AS UNSIGNED);
    ELSE
        RETURN 0;
    END IF;    
END$$
DELIMITER ;

Now you can do this:

SELECT ExtractNumber(my_field)
FROM my_table;

select only digits, numbers from text

3 Answers3