CODEWITHSUNDEEP
sql-servert-sql
UserSN
UserSN

Reputation: 1013

Entering a comma delimited string into an INT column in SQL

I'm trying to add a comma delimited string into a column in my table set to INT. I used it to reference a Category ID but now I'd like the possibility to add more than just 1 category.

Thinking I could accomplish this by entering a string like: 1,2,3 instead of just 1 but i'm getting errors that the changed value in this cell was not recognized as valid.

Does this mean I need to change that column to VARCHAR instead of INT?

Upvotes: 0

Views: 525

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

No, this means that you should set up proper tables to support a 1-many relationship. That is, you want a separate table, not a bogus data representation in a varchar column.

Why? Well, SQL has this great data structure for lists, called a table. In addition:

  • Numbers should be stored in native format, not a string.
  • SQL has (relatively) poor functions for manipulating strings.
  • Operations on the column will not take advantage of indexes.
  • If the numbers represent ids in another table, you cannot declare proper foreign key references.

Upvotes: 7

Related Questions