There is a table that has a column defined as follows:
text_id varchar(255) NOT NULL I know that using varchar is optimal for storing text because it allows for variable length columns but in this case I am not sure if there is any id stored that is not in the form of a number so it is not clear to me if there is any benefit to store it in this data type instead of e.g. unsigned int.
Is there any benefit using this definition and how can I check if there is any value in the column that is not a numeric but some form of alphanumeric id?
I tried where text_id REGEXP '^[A-Za-z0-9]+$' limit 10 but this matches also pure numbers.

Note: the table has too many rows to just do a SELECT * and review


2 Answers 2


Your regular expression is looking for strings that contain only alphanumeric characters, that is why it picks out pure numbers – you've told it to look for them too. It breaks down as:

    ^            = start of string
    [A-Za-z0-9]+ = one or more letter or number character
    $            = end of string

To match those that are pure numbers you want REGEXP '^[0-9]+$' and you can invert the logic with WHERE NOT (text_id REGEXP '^[0-9]+$') to find problem rows. Possibly not the most efficient way but this sort of lookup is going to table-scan (or at beat index-scan) so I expect nothing will be significantly better.

To answer the general question:

Is there any benefit using this definition [varchar for an ID]

The only benefit is if an ID could be not purely numeric. To know if that might be the case you need to know the system and its data sources so we can't answer that. Perhaps the DB designer explicitly picked a text representation because they can't control what future systems integrating with this one might want to use so couldn't dictate that this identifier will always be numeric going forward even if it is always numeric currently.

Generally speaking if the IDs are always going to be numeric then using an integer type of appropriate size will be more efficient, in terms of both storage and CPU use, than a text representation. There is one thing to be careful of if moving from text representation of identifiers to a numeric representation which is leading zeros: sometimes systems enter something like 000123 as an ID string and expect to get 000123 back when they read, which they won't with an integer type (the leading zeros will be lost).

Even if you are sure the IDs are always numeric and won't have leading zeros, make any change with caution: if an application is expecting a string type it may fail even if the data represented is no different. Check your change in a test environment before doing it in production. Also you may need to drop foreign-key constraints referring the ID before you can change the type, and need to change the type of the referring columns before putting the FK back, so it could be a much larger job than just updating this one column.

One further point: as the column is explicitly named text_id, changing its type to a numeric will make that contradictory and confusing.

  • In regards to your last point. The column is named with the suffix id. So why would changing its type make it contradictory and confusing?
    – Jim
    Jul 10 at 12:05
  • @Jim Because the prefix is text_ which would indicate it's a string-like data type.
    – J.D.
    Jul 10 at 12:35
  • @J.D.: The way I read it is that it is the id for some text defined elsewhere. But may be it is only me who reads it like that
    – Jim
    Jul 10 at 14:44
  • @Jim It's a fairly common (though silly) pattern that developers name things with meta-data such as the datatype of the column. So it's not unusual to see columns with the word text in them with the intention to indicate it's a non-numerical field. I'm not sure of your schema (since it wasn't provided in the question) but I understand your interpretation too, assuming you have another table for an object known as Text. Though that sounds too generic of a name if that's the case and probably should be better qualified with more nouns, e.g. PostText if it were the body of a forum post...
    – J.D.
    Jul 10 at 14:51
  • ...which would then result in the id column being called PostTextId for example - something slightly more readable.
    – J.D.
    Jul 10 at 14:53
WITH cte (text_id) AS (
  SELECT '1' UNION ALL   -- number
  SELECT '123' UNION ALL -- number
  SELECT '1a1' UNION ALL -- not number
  SELECT '  2' UNION ALL -- not number
  SELECT '3  ' UNION ALL -- not number
  SELECT 'asd' UNION ALL -- not number
  SELECT 4               -- number
FROM cte
WHERE text_id NOT REGEXP '[^\\d]'


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.