[Seavox] FW: underscores in conceptid in VOCAB

[Lowry, Roy K.]

Dear All,

Serge van der Horst from Maris has pointed out that the some of the codes in the C19 sea area vocabulary that were inherited (I wouldn't use them from choice!) from one of the sources contain underscores, which is the single-byte wildcard used in some (if not all) variants of SQL. Unless programmed around, this can produce unexpected results when searching concatenated lists of codes (see Serge's description of the problem below).

The workaround I would use in Oracle SQL is to use the replace function to dynamically substitute the underscores by something else - in this case hyphens:

select * from table where ','+field+',' like '%,3_1_1_5,%'

by

select * from table where ','||replace(field,'_','-')||',' like replace ('%,3_1_1_5,%','_','-')

This certainly affects the EDMED web application


Cheers, Roy.




From: Serge van der Horst [serge at maris.nl]
Sent: 18 October 2013 17:20
To: Lowry, Roy K.; Leadbetter, Adam
Cc: 'Dick M.A. Schaap'; 'Peter Thijsse'; 'Bert Broeren'
Subject: underscores in conceptid in VOCAB


Dear All,

I discovered today a problem with the conceptid codes in the Vocabs.

I'll illustrate the problem with an example:

I'm searching for a sea-area with conceptid 3_1_1_5 (this is the Tyrrhenian Sea)

In my main table there is a field sea-areas and in that field there can be multiple sea-areas.(comma separated)

For example a record in this table has the following values in the field sea-areas

1_2,1_7,1_8,3_1_1,5,SVX00015,SVX00025

So summarized:

I'm searching for the value 3_1_1_5 in the string 1_2,1_7,1_8,3_1_1,5,SVX00015,SVX00025

When I make an sql statement it looks like

select * from table where ','+field+',' like '%,3_1_1_5,%'

(You have to know that a underscore is a wildcard character in sql)

So the record with this value 1_2,1_7,1_8,3_1_1,5,SVX00015,SVX00025 gives a match.

Because 3_1_1_5 'likes' 3_1_1,5 (the statement doesn't look to the comma in 3_1_1,5 because in the like statement there is an underscore at that position and that's a wildcard)

Solution:

In sql it's possible to look for an underscore but then the statement should be:

select * from table where field like '%,3[_]1[_]1[_]5,%'

(you have to to put brackets around the underscores and you don't have to manipulate the data)

But in general you don't want to replace everytime the underscores by bracket-underscore-bracket.

The best solution is do not use underscores in the conceptid's

On our site we use SQL Server but I think also the ORACLE users will have this problem.

Te results of a query are not always reliable with the problem described above.

Please forward this mail to everybody who might have the same problem.

Kind regards,

Serge van der Horst


MARIS BV
Koningin Julianalaan 345 A
2273 JJ Voorburg
Tel: 070-3004710
Fax: 070-3903546
Zie ook: www.maris.nl

This message (and any attachments) is for the recipient only. NERC is subject to the Freedom of Information Act 2000 and the contents of this email and any reply you make may be disclosed by NERC unless it is exempt from release under the Act. Any material supplied to NERC may be stored in an electronic records management system.