In my database, I have columns that should contain text pieces.

  • What is the best data type used for texts in SQL?
  • How can I show that text as a numbered or bullet list?
  • Can I search for a certain word or sentence in that text?
  • Can I make some words in the text piece as a hyperlinks to refers to another columns content?

Example about the Augmentin medicine

  • Augmentin is a penicillin antibiotic.
  • Augmentin contains a combination of amoxicillin and clavulanate potassium.
  • Amoxicillin is an antibiotic in a group of drugs called penicillins.
  • Clavulanate potassium is a form of clavulanic acid, which is similar to penicillin.
  • Augmentin is used to treat many different infections caused by bacteria, such as sinusitis, pneumonia, ear infections, bronchitis, urinary tract infections, and infections of the skin.

1 Answer 1


Data type - VARCHAR for ANSI which uses 1 byte per character or NVARCHAR for UNICODE which uses 2 bytes per character. In your case, I would suggest NVARCHAR, so you can store multiple languages within the same columns. Avoid TEXT and NTEXT as they are deprecated.

Searching - It is possible to use LIKE predicate, for simple searching with Wild Cards, but I suggest you investigate the use of Full Text indexing which will increase query performance and also allow you to use the FULLTEXT predicate for very flexible fuzzy searching.

Bullets and hyperlinks - I think you are thinking of SQL in the wrong way here. I think you should be thinking of those tasks within your client application. To facilitate this from the back end, hyperlinks I would split into a separate table with a key that you can then report on. For bullets, again strongly suggest controlling this through the client side, but if you really wanted to, you could insert a U+2022 at the beginning of each row. If there are multiple bullets per row of data, you could store this char at the beginning of each bullet and then use a string splitting function when reading from the table. Many ways of accomplishing this but suggest using XML sub query.

  • @PeterSmith .. so u prefer to insert text as a complete block and manage it later through application itself ..right? Oct 8, 2012 at 5:25
  • Well, with the limited sample data I am assuming that these are descriptions of medicines. If so, that would logically equate to one description per row with a possible link to an additional hyperlinks table Oct 8, 2012 at 5:38
  • so if i talk about medicine composition .. which may be up to 10 chemicals ..i can but them all as a text and show them later as numbered list .. rather than define a column for each chemical from the 10 Oct 8, 2012 at 5:50
  • If there are multiple chemicals per medicine then you have a standard many to many relationship. Store the medicines in one table with a relationship to the chemicals in a different table. One row per medicine, one row per chemical Oct 8, 2012 at 5:55

Your Answer

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

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