1

I have a field ERR_CODES on my db that contains some error codes for the current record.

For example, it can contain:

Invalid_Template_Code_Filename_WCLS,Color_Template_Of_Different_Domain_WCLS,Section_Not_Found_WCLS

Suppose I want to test if it contains Section_Not_Found_WCLS, without adding a comma at the start and at the end of the field.

I should test for:

WHERE "ERR_CODES" LIKE '%,Section_Not_Found_WCLS,%'  --between other errs
   OR "ERR_CODES" LIKE 'Section_Not_Found_WCLS,%'    --at start of a err sequence
   OR "ERR_CODES" LIKE '%,Section_Not_Found_WCLS'    --at end of a err sequence
   OR "ERR_CODES"   =  'Section_Not_Found_WCLS'      --unique err for this record

This is not really beautiful.

Is there a way to compact that, using wildcards?

EDIT:

In RegEx this would be ((?<=,)|^)Section_Not_Found_WCLS((?=,)|$)

Ideally, I want a solution that will work on each of Oracle, MSSQL, PGSQL.

Taryn
  • 242,637
  • 56
  • 362
  • 405
Teejay
  • 7,210
  • 10
  • 45
  • 76

2 Answers2

1
WHERE "ERR_CODES" LIKE '%Section_Not_Found_WCLS%'

EDIT:

 WHERE ',' + "ERR_CODES" + ',' LIKE '%,Section_Not_Found_WCLS,%'
Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
  • 1
    No, this will return `TRUE` even for `My_Section_Not_Found_WCLS` – Teejay May 07 '13 at 09:36
  • 1
    Yes, I thought about the solution proposed in your edit. This would be a good solution. (anyway, I think you missed a % at the end, didn't you?) – Teejay May 07 '13 at 09:39
  • 1
    Yes, thanks. Fixed. Based on RDBMS syntax for string concatenation might be different. I am not sure if it will work same on other then SQL Server. – Nenad Zivkovic May 07 '13 at 09:48
  • That's OK indeed. I have a custom Query Builder that builds concatenation based on the current DBMS :) – Teejay May 07 '13 at 09:58
  • @Teejay There is to some degree, but it's not that complex and again differ greatly for each RDBMS. Check T-SQL examples at http://msdn.microsoft.com/en-us/library/ms179859.aspx – Nenad Zivkovic May 07 '13 at 10:56
  • Unfortunately, `%`, `_` and `[]`/`[^]` char classes ***seem*** not sufficient to achieve my result. – Teejay May 07 '13 at 11:02
1

Another possible thing to consider, is making the database more relational as it is the standard.

Rather than keeping a bunch of strings in a line, make a extra table, to keep things more organized. I have created a image to depict exactly what I mean.

Suggestion

Corne
  • 496
  • 2
  • 8
  • 22
  • Good point, but this is actually a batch generated temporary table and storing err_codes in another table is not worthy. – Teejay May 07 '13 at 09:40