Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote -1 down vote favorite
1

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.
share|improve this question

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
@PeterSmith .. so u prefer to insert text as a complete block and manage it later through application itself ..right? – Hatem Ghazy Oct 8 '12 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 – Pete Carter Oct 8 '12 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 – Hatem Ghazy Oct 8 '12 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 – Pete Carter Oct 8 '12 at 5:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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