Menu
i have a mysql table with this sort of data
TACOMA, Washington, 98477
Now i have thousands of such rows. I want the data to be manipulated in such a manner that it appears like:
TACOMA, Washington
Is it possible though mysql or do i have to manually do it.
511
SQL Server TRIM() Function The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
In order to delete everything after a space, you need to use SUBSTRING_INDEX(). Insert some records in the table using insert command. Display all records from the table using select statement.
SELECT TRIM(BOTH ',' FROM ',,,demo, ,xxxx,,,yyy,,,'); SELECT REPLACE(TRIM(TRIM(',' FROM ',,,demo, ,xxxx,,,yyy,,,')), ',,', ',');
Below is the syntax for the SUBSTRING() function to delete the last N characters from the field. Syntax: SELECT SUBSTRING(column_name,1,length(column_name)-N) FROM table_name; Example: Delete the last 2 characters from the FIRSTNAME column from the geeksforgeeks table.
You can use :
SELECT SUBSTRING_INDEX('TACOMA, Washington, 98477', ',', 2)
You can read more here.
And the update statement :
UPDATE my_table
SET my_col = SUBSTRING_INDEX(my_col, ',', 2)
Where you need to replace my_table with your table name and my_col with the column you need to be updated.
61
Possibly this way. Count the number of commas (by checking the length against the length with all the commas removed) and then use SUBSTRING_INDEX to get the string up to the number of commas:-
SELECT SUBSTRING_INDEX(col, ',', LENGTH(col) - LENGTH(REPLACE(col, ',', '')))
FROM SomeTable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
