select only numeric values on sql

Question

I know there is already some of the same questions out there, but this one is confusing me.

I have this query :

SELECT 
    CASE
    WHEN COALESCE(substring(location_name FROM '[0-9]+'), location_name) != '' 
    THEN COALESCE(substring(location_name FROM '[0-9]+'), location_name)
    ELSE '1'
END AS sequence
FROM LOCATION

What I would like to get from that query is :

If the location_name doesn't contains any numeric values, then return 1
If the location_name contains numeric values, get only the last numeric values (after string), i.e. c2 carousel 10, should return only 10
If the location_name contains only numeric values, then return 1

But what I get is something like :

location_name   expected result     what I get
                                    using [0-9]         using [0-9]+
carousel 1      1                   1                   1
carousel 2      2                   2                   2
carousel 3      3                   3                   3
carousel 12     12                  1                   12
bottom banner   1                   bottom banner       bottom banner
c2 carousel 1   1                   2                   2
c2 carousel 3   3                   2                   2
59977           1                   5                   59977

Is it possible to do this in sql?

mysql regex return only Boolean you can't replace/filter text using regex, you should create custom string manipulation function to extract result — Girish
– Girish, Commented Dec 11, 2014 at 6:06
Take a look at my answer. I think it is what exactly you want. — Veera
– Veera, Commented Dec 11, 2014 at 7:10

Veera · Accepted Answer · 2014-12-12 06:17:38Z

1

Try this: (I think it will return the expected result. Else make a note what you get.)

SELECT location_name, 
CASE WHEN concat('',location_name * 1) = location_name THEN
     1
WHEN concat('',reverse(substring_index(reverse(location_name), ' ', 1)) * 1) = reverse(substring_index(reverse(location_name), ' ', 1)) THEN
    reverse(substring_index(reverse(location_name), ' ', 1))
ELSE
    1
END AS EXPECTED_RESULT
FROM YourTable

--Quick Demo here:MySQL

It is not good when you suddenly changed the tag MYSql to PgSQL. All my works got waste.

Here is the PgSQL code:

Create a function to check isdigit():

create function isdigits(text) returns boolean as '
select $1 ~ ''^(-)?[0-9]+$'' as result
' language sql;

Then here is the code:

   SELECT location_name, 
      CASE WHEN isdigits(location_name) = true THEN 1
           WHEN isdigits(substr(location_name, length(regexp_replace(location_name, '\\s\\S+$', '')) + 2)) = true THEN 
                substr(location_name, length(regexp_replace(location_name, '\\s\\S+$', '')) + 2)::int
           ELSE 1
      END AS Result
    FROM YourTable

--Quick Demo - PGSQL.

edited Dec 12, 2014 at 6:17

answered Dec 11, 2014 at 6:23

Veera

3,4922 gold badges16 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mrjimoy_05 Over a year ago

Wow it's great, but why I get HINT: No function matches the given name and argument types. You might need to add explicit type casts. (it's pointing to REVERSE) ?

Veera Over a year ago

I changed my answer for PgSQL and added link for demo too.

Ronak Shah · Accepted Answer · 2014-12-11 06:32:54Z

0

-- Create one function first which will help to check wether value is numeric or not

CREATE FUNCTION mysql_IsNumeric(val TEXT) RETURNS int(11)
RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

--create function that return numeric values only    
CREATE FUNCTION mysql_NumericOnly(val TEXT) RETURNS text CHARSET latin1
BEGIN  
DECLARE idx INT DEFAULT 0;  
IF mysql_IsNumeric(val) = 0 THEN  
IF ISNULL(val) THEN RETURN NULL; END IF;  
IF LENGTH(val) = 0 THEN RETURN ""; END IF;  
SET idx = LENGTH(val);  
WHILE idx > 0 DO   
IF strcmp(SUBSTRING(val,idx,1),'.')!=0  and mysql_IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN  
SET val = REPLACE(val,SUBSTRING(val,idx,1),"");  
SET idx = LENGTH(val)+1;  
END IF;  
SET idx = idx - 1;  
END WHILE;  
END IF;  
RETURN val;  
END;


select *,
 case when mysql_IsNumeric(location_name)=1 then 1
 case when location_name!=mysql_NumericOnly(location_name) THEN
mysql_NumericOnly(Reverse(Left(REVERSE(location_name),INSTR(REVERSE(location_name),' ')))
  else 1 end as reselut    
  from YourTable

answered Dec 11, 2014 at 6:32

Ronak Shah

1,5592 gold badges13 silver badges20 bronze badges

1 Comment

mu is too short Over a year ago

The OP has switched the tags from mysql to postgresql so I don't think your answers applies anymore.

Collectives™ on Stack Overflow

select only numeric values on sql

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related