Using CAST with WHERE

Question

I have this table(students):

id | name  | address
1  | hello | test451.1
2  | hello | test461.1

I want to select the rows that contain 451 in the address field exact before decimal and after alphabet string. Basically I want to filter 451 from address values. I am using the following query to get integer out of string.

SELECT * FROM `students` WHERE CAST(`address` AS UNSIGNED) = '451'

I still have no idea how to filter for a float.

Also tried this but didn't work :

SELECT * FROM `students` WHERE CAST(`address` AS DECIMAL(4,1)) = '451'

I can't use like %451% because there are other rows with test4451.1, test 5441.1 etc. I don't want to select them.

Zaynul Abadin Tuhin · Accepted Answer · 2018-07-24 15:51:06Z

1

use substring and position to figure out 451

SELECT * FROM `students` WHERE  
substring(address,position('4' in address),4)= '451.'

here is the link i try with your data and it returns

create table students (
id int,name varchar(50),address varchar(300)
);
insert into students values(1,'hello','test451.1'),(2,'hello','test461.1'),(3,'hell','test 5441.1');

id  name    address
1   hello   test451.1

https://www.db-fiddle.com/f/c6byWF6ahoBusWQqqNtsSf/0

edited Jul 24, 2018 at 15:51

answered Jul 24, 2018 at 13:45

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

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

1 Comment

Jasminder Pal Singh Over a year ago

There is one problem. Its also picking up the rows which has values like test4511.1 . It shouldn't select those.

Micha Wiedenmann · Accepted Answer · 2018-07-24 12:51:45Z

0

SELECT CAST(25.65 AS int);

That is what W3schools says. That should get the integer out of string.

edited Jul 24, 2018 at 12:51

Micha Wiedenmann

21.1k22 gold badges96 silver badges142 bronze badges

answered Jul 24, 2018 at 12:51

Daniel Bailey

9895 gold badges18 silver badges36 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-07-24 13:12:58Z

0

I think you want like:

where address like '%451%'

You can use:

where address like 'test451.%'

edited Jul 24, 2018 at 13:12

answered Jul 24, 2018 at 12:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

Jasminder Pal Singh Over a year ago

I can't use like because there are other rows with address as test4451, test5451 . I must use = .

Gordon Linoff Over a year ago

@JasminderPalSingh . . . It is unclear what you are asking. Why not just use =?

Jasminder Pal Singh Over a year ago

Thanks for responding. Actually, I can't use = because the user input coming as integer(451) but field has value in string format having float. So i must filter exact results.

Jasminder Pal Singh Over a year ago

Please check Zaynul Abadin Tuhin answer. It worked but there is one issue as written in answer comment.

Collectives™ on Stack Overflow

Using CAST with WHERE

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related