CODEWITHSUNDEEP
sqlteradatateradata-sql-assistant
matc
matc

Reputation: 29

Creating a column displaying query match

I would like to ask if anyone could offer any advice on Teradata/SQL queries. As part of my day to day responsibilities, I often run string/text/batch searches across a database for large lists of strings/names (e.g., animal species A, B, C, D and these lists could run into 700 or even 1,100 names at a time). I have no issues with pulling and exporting the data via the "LIKE ANY" statement and some '%' characters preceding and following each string.

The issue I come across is SQL/Teradata will give me what I need, but my statement does not highlight which entity was found per a specifc row of data returned from the list I used. In addition, some of the strings can be found across 2-3 different columns, not just 1 column) For example, if I have 700 species of animals and my statement returns 25,000 data points, it would be very beneficial for which string a match was found in a given column and avoid manually creating an additional column and reviewing the data in e.g., excel.

For example, an additional column "Name/entity found for Column 1" where it will display, e.g., match for row 2 was " dog species 124" , row 1,023 was " cat species 456" would prove extremely useful.

Below I am attaching an example statement:

      SELECT *
        FROM Table_Animal_Species
        WHERE Date BETWEEN '2005' AND '2010'
        AND
        (
    SpeciesColumn1 LIKE ANY ('%cat 123%', '%dog 456%') Or
    SpeciesColumn2 LIKE ANY ('%cat 123%', '%dog 456%') Or
    SpeciesColumn3 LIKE ANY ('%cat 123%', '%dog 456%') Or
          )

Thank you in advance for any advice!

Upvotes: 0

Views: 226

Answers (1)

dnoeth
dnoeth

Reputation: 60482

If a column contains only one match you can simply switch to a RegEx instead of LIKE. It's also easier to write, names separated by |, but you probably create that list instead of keying it in :-)

SELECT 
   regexp_substr(SpeciesColumn1, 'cat 123|dog 456', 1, 1, 'i') AS found_in_column_1
  ,regexp_substr(SpeciesColumn2, 'cat 123|dog 456', 1, 1, 'i') AS found_in_column_2
  ,regexp_substr(SpeciesColumn3, 'cat 123|dog 456', 1, 1, 'i') AS found_in_column_2
  ,t.*
FROM Table_Animal_Species AS t
WHERE found_in_column_1 IS NOT NULL
   OR found_in_column_2 IS NOT NULL
   OR found_in_column_3 IS NOT NULL

Upvotes: 1

Related Questions