Using MySQL 5.6. I have a varchar column that stores addresses as text. When they come through to my application into a text area box, they are spaced appropriately, like I tried inserting a new address directly into the database but it doesn’t work correctly inside the GUI, it all came in as one line w…
Tag: mysql
Changing an entire column from YYYY-MM-DD to number of months in MySQL
Suppose you have an entire column in MySQL that is YYYY-MM-DD, and you want to select it as the number of Months to a specific date, and also do it as a new column. Example date counting to can be March 2020. So the following (table_1): Emp_Name Hire_Date Steve 2018-03-28 To Emp_Name Months_Employed Steve 24 …
show who resit and passed and what course was it?
i need help find who fail in exam & resit and pass the exam only, heres the code: heres the result: I need find like this: Answer Possibly using a query like below. this is using your query as input. Also we have assumed that it is not possible to have a student have (PASS, FAIL) for a student on
Select with joins in a unique table system
I am trying to build an SQL query on an omekaS database to extract books with a specific title and date. In this system, the table holding the values is called value, and the relevant attributes are as so : The resource_Id is the identifier through the db Value is one field; like the book “Moby DickR…
How to Get only number from string mysql
I have a column called Equipment in my table that contains something like this: store(2) I need to create a new column and select only the number from the text otherwise we put 1. Example: Thanks. Answer You can use the REGEXP_SUBSTR function for this.
MySQL alternative way to `CASE` Statement
May I know, is there any alternative way or short way to write this case statement shown in the below: I tried using mysql built in funciton ifnull as below: ifnull(p.our_price,p.sales_price) But it doesn’t work for me. Answer You could stick with a CASE expression but use COALESCE to make it more succi…
Does the column order matter in a WHERE SQL statement
I’m working on a system with a good amount of indexes. Some indexes are simpler than others. I.e. they’re INT, VARCHAR, DATETIME and in some cases ENUMS(maybe 5~25 variations). Does the WHERE order matter? In other words, would placing the easier to search columns first increase speed/performance?…
How do I add multiple values to a column in SQL which already has other rows filled
I’m trying to update this table (name: sports_club): I’m trying to add values to just the sport_interest column of the existing records, I tried to do that by using: But it did not work as it was trying to add it to new records which I don’t want to do. I’m using MySQL Answer You can d…
SQL Query, What have I done wrong? I am fairly new to mySQL
Solution problem solution uploaded Answer You have to tell the count() function what to count. You can insert an individual column, or * for all of it etc. But you have to count something. This is a fiddle, showing how it works: https://www.db-fiddle.com/f/dbPnE4BXv8oRRkQY4WQs8v/1
How to parse a string and get the value after “=” character
So I have string that should contains “Object.Name” once in a row , if I see it ,I have to get the value after “=” character. If doesn’t match it anywhere in the string i should move hardcoded value. Here is example of the string: I know that I should use case for that but doesn&…