MySQL <= string problem

Okay, so I've got a MySQL database here that I'm working with that is already pre-populated with data.

Only thing is - the guy that designed the structure set it up in such a way where one of the rows in a table which should contain numeric data actually contains string data...

Think "six" instead of 6.

Luckily there are only ten different possibilities...so everything is either "one", "two", "three", "four", "five", etc...up to "ten"

Problem is - I need to do a comparison in my query..  Kindof like "WHERE row <= whatever".

How would you go about approaching this?  Is there a way to do 10 conditionals in MySQL to say

if row = "ten" row = 10?

Thanks in advance!
LVL 2
erzoolanderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Replace @f with your field name and also add From TableName:
Declare @f varchar(10) = 'six';
Select case @f
       When 'one' then 1
       When 'two' then 2
      When 'three' then 3
      When 'five' then 5
	   else 0
	   End NumField
--From TableName

Open in new window


These this to see if it works in MySQL then add more case lines as needed.
erzoolanderAuthor Commented:
Okay - forgive my question - but how would I fit that into this query?

SELECT * FROM field_data_field_rate_dates t1
	LEFT JOIN field_data_field_room_rates t3 on t3.field_room_rates_target_id = t1.entity_id
	LEFT JOIN field_data_field_hotel_rooms t4 on t4.field_hotel_rooms_target_id = t3.entity_id
        LEFT JOIN field_data_max_number t6 on t6.entity_id = t4.entity_id
	WHERE t1.field_rate_dates_value <= '2015-07-11'

Open in new window


the field in this query would be:

t6.field_max_number_value
Mike EghtebasDatabase and Application DeveloperCommented:
	SELECT *, 
	   case t6.field_max_number_value
           When 'one' then 1
	   When 'two' then 2
	   When 'three' then 3
	   When 'four' then 4
	   When 'five' then 5
	   When 'six' then 6
	   When 'seven' then 7
	   When 'eight' then 8
	   When 'nine' then 0
	   When 'ten' then 10
	   else 0
	   End NumField
    FROM field_data_field_rate_dates t1
	LEFT JOIN field_data_field_room_rates t3 on t3.field_room_rates_target_id = t1.entity_id
	LEFT JOIN field_data_field_hotel_rooms t4 on t4.field_hotel_rooms_target_id = t3.entity_id
        LEFT JOIN field_data_max_number t6 on t6.entity_id = t4.entity_id
	WHERE t1.field_rate_dates_value <= '2015-07-11'

Open in new window


You need to decide what if something other than 'one' through 'ten' is in the field then change the following line accordingly:

else 0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
erzoolanderAuthor Commented:
Okay - one last stupid question...lol  Thank you so much - it's working properly from what I can see.

in the WHERE clause..

WHERE t1.field_rate_dates_value <= '2015-07-11'
AND NumField >= 3

It says that NumField is an unknown column?   I see it being output..  Is there a special naming convention to access it since it was dynamically created?
Ray PaseurCommented:
Two thoughts...

1. Check the comment here - looks like a typo:
	SELECT *, 
	   case t6.field_max_number_value
           When 'one' then 1
	   When 'two' then 2
	   When 'three' then 3
	   When 'four' then 4
	   When 'five' then 5
	   When 'six' then 6
	   When 'seven' then 7
	   When 'eight' then 8
	   When 'nine' then 0 /* *** IS THIS RIGHT? *** */
	   When 'ten' then 10
	   else 0
	   End NumField
    FROM field_data_field_rate_dates t1
	LEFT JOIN field_data_field_room_rates t3 on t3.field_room_rates_target_id = t1.entity_id
	LEFT JOIN field_data_field_hotel_rooms t4 on t4.field_hotel_rooms_target_id = t3.entity_id
        LEFT JOIN field_data_max_number t6 on t6.entity_id = t4.entity_id
	WHERE t1.field_rate_dates_value <= '2015-07-11'

Open in new window

2. Maybe use ALTER TABLE to add the numeric column?  Then you would not need to repeat the alpha-to-number translation in every query.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.