Search in a column containing comma-separated values I ended up using SUBSTRING_INDEX() to order the results by one of the fields.
For example, recently I needed to return a list of values from user-defined function and the only way to do it would be to return a string containing comma-separated values. However, there could be rare cases where the trick above could be useful. In most cases it is better to use a separate lookup table or a column for each field instead of storing data as comma-separated values for later lookup.
#Stri gsplit php code
One possible workaround to this problem would be to check if the number of fields is greater than or equal to requested index and then the code the above, or return empty string, if the field index is invalid. SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 6), ',', -1) AS country FROM employees
#Stri gsplit php zip
Then attempt to extract sixth field with SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 6), ',', -1) will give us zip code (fifth field) which is wrong. | Bob Smith | 234 Main Street,Erie,PA,16510 | | name | SUBSTRING_INDEX(address, ',', 6) | SELECT name, SUBSTRING_INDEX(address, ',', 6) FROM employees
#Stri gsplit php full
One thing to be aware about SUBSTRING_INDEX() is that the function returns the whole string if the requested field doesn’t exist.įor example, SUBSTRING_INDEX(address, ',', 6) returns full address. SELECT name FROM employees WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 4), ',', -1)='PA'
SELECT name, SUBSTRING_INDEX(SUBSTRING_INDEX(address, ',', 4), ',', -1) AS state FROM employees įinal solution to get all employees working in a certain state is shown below. Because SUBSTRING_INDEX() function allows to use negative values to extract fields counting from the right, we will use this ability to extract the rightmost field containing state abbreviation. Now we know that state would always be the last field in the resulting value. | name | SUBSTRING_INDEX(address, ',', 4) | With SUBSTRING_INDEX(address, ',', 4) function we will be able to extract everything up to the fourth column containing state SELECT SUBSTRING_INDEX(address, ',', 4) FROM employees Suppose we want to find all employees working in a certain state (for example, PA) which is a fourth field in a column holding address as comma-separated values. (1, 'John Doe', '4225 Collins Street,Apt. INSERT INTO employees (id, name, address) VALUES SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.
If count is negative, everything to the right of the final delimiter (counting from the right) is returned. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. Returns the substring from string str before count occurrences of the delimiter delim. Luckily it has SUBSTRING_INDEX() function that does almost what we need. Surprisingly MySQL doesn’t have a dedicated function for this operations as opposed to split_part in PostgreSQL. Split comma-separated values and retrieve a value at certain position
0 kommentar(er)
