1

I am dealing with a column named UCODE130 that has values anywhere from 1-130. I would like to replace the values with ranges. For example, I want to replace any 1, 2, 3, or 4 found in that column with a string "1 - 4" instead. I do not want to do just a select statement - I want to actually change the column in the database itself.

The other issue I have is that these ranges are all different sizes. I would need something like:

1,2,3,or 4 becomes "1 - 4"

5,6 becomes "5 - 6"

7,8,9,10,11,or 12 becomes "7 - 12"

and so on.

I need to recode or "find and replace" the values to get this to work. There are over 20,000 rows in my column so I was hoping there was an efficient way to do this. Please let me know if more information is needed. Thank you!

forpas
  • 160,666
  • 10
  • 38
  • 76
emismi95
  • 17
  • 4

2 Answers2

1

Always risky to replace data. A calculation in SELECT query can produce the same dataset. Also you want to replace a number value with a string. That cannot be done if the field is a number type. If it is not, then the numbers will be treated as alpha-numeric, not number, i.e. 12 will sort before 2.

In Access I would use intrinsic Switch() function (see example SELECT query below) or call a custom VBA function. However, in MySQL can use CASE structure in SQL statement.

SELECT *, 
Switch(UCODE130 < 5, "1 - 4", UCODE130 < 7, "5 - 6", UCODE130 < 13, "7 - 12") AS NewVal 
FROM table

If you must have this value in table, one approach is to create another field of text type and update that field with expression. Then if you really, really want to can delete the original number field. Or convert the field to text type so it can be updated with string.

If the field is a text type, MySQL should implicitly cast the field value to a number in expression.

June7
  • 19,874
  • 8
  • 24
  • 34
0

You can define the sizes with a CASE expression in the UPDATE statement:

UPDATE tablename
SET UCODE130 = CASE
  WHEN UCODE130 BETWEEN 1 AND 4 THEN '1 - 4'
  WHEN UCODE130 BETWEEN 5 AND 6 THEN '5 - 6'
  WHEN UCODE130 BETWEEN 7 AND 12 THEN '7 - 12'
END
WHERE UCODE130 BETWEEN 1 AND 12

or:

UPDATE tablename
SET UCODE130 = CASE
  WHEN UCODE130 <= 4 THEN '1 - 4'
  WHEN UCODE130 <= 6 THEN '5 - 6'
  ELSE '7 - 12'
END
WHERE UCODE130 BETWEEN 1 AND 12

Note that for this to work the column's data type must be VARCHAR.
If it is INTEGER, first change its data type:

ALTER TABLE tablename MODIFY id VARCHAR(10);
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Will `BETWEEN 1 AND 4` or `<=4` work properly when field is VARCHAR type? Will 12 fall between 1 and 4? – June7 Apr 22 '21 at 17:19
  • @June7 MySql does implicit conversions: https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html – forpas Apr 22 '21 at 17:21
  • Access does the same thing, however, as that article states, only SOME conversions occur implicitly. The SQL in Access did not implicitly convert field to number in the expression. Had to use a function like Val(). Does MySQL? – June7 Apr 22 '21 at 17:27
  • @June7 This question is about Mysql and not about Access. The documentation in the link of my previous comment has many examples of conversions that happen implicitly and the OP's requirement is covered fully because all the string values in the column UCODE130 are numbers stored as strings. – forpas Apr 22 '21 at 17:32
  • Yes, I know that and I read the article. Just wanted to confirm MySQL would treat the text field as a number and properly assign string. Because there was only one example that utilized a field and seems to me it said comparison would NOT work with string column. – June7 Apr 22 '21 at 17:46
  • @June7 read carefully the documentation. Which example did you find that said it would not work? Also you can try it yourself in a fiddle: https://www.db-fiddle.com/f/j4CacsS4QURpuhkio9P3nN/0 – forpas Apr 22 '21 at 17:52
  • Most likely my lack of understanding: **For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly.**. However, your Fiddle does do implicit conversion of `'11'` to number, so I presume it will work with field as well. This fails in my Access query. Thanks for the educational moment. – June7 Apr 22 '21 at 18:01