SELECT {*|<Col1>}, <Col2>, <Col3>…
FROM <table name>
WHERE <column name> BETWEEN <lowest values> AND <highest value>
SELECT {*|<Col1>}, <Col2>, <Col3>…
FROM <table name>
WHERE <column name> LIKE [ALL | ANY] ‘[% | _] character string [% | _]’
To use of LIKE operator effectively, you have to provide the right pattern to it and never forget to use the ’single’ quotes. One good thing about the LIKE operator in Teradata SQL is that we can pass multiple values (pattern) for our search using ALL or ANY operator, which I do not think is present in any other database environment. Here is any example;
SELECT First_Name, Last_Name
FROM employee
WHERE Last_Name LIKE ANY (‘%Sim%’,’ %Pet%’)
The above would return rows if either of the matching pattern found
SELECT First_Name, Last_Name
FROM employee
WHERE Last_Name LIKE ALL (‘%Sim%’,’ %Pet%’)
The above would return rows only if all of the matching pattern found. If you have an underscore present in your data value and you want that to come in your result set than you have to the escape operator to include the underscore as a value and not as a wildcard character. Here is the way
SELECT First_Name, Last_Name
FROM employee
WHERE Last_Name LIKE (‘%S\_%’) ESCAPE ‘\’
The above query would find results like S_123, S_mon, s_TUE, s_533T etc. Note that the underscore is present at the 2nd position of the string.
Popularity: 29% [?]
Our Random Articles
- New Year Wishes 2010
- New Year Wishes
- How to use EXTRACT function with date and time columns
- How to Post on a Separate/New Page in WordPress
- Usage of Macro in Teradata
More Links
5 Comments
This is great, this is exactly what I was looking for. Thanks
how to case sensitive in like search
Hi Tintin,
If you are in Teradata mode then you do not have to worry as in Teradata mode, database is not case sensitive. Only if you are in ANSI mode, you need to write exact case. For example, if you have first name as tintin in a database, then LIKE ‘tin%’ would return “tintin” both in Teradata as well as ANSI mode but LIKE ‘Tin%’ would only return in Teradata mode.
Hope that helps.
I want a SQL LIKE on a column say (EMP_ID varchar(30)) where EMP_ID contains a blank space anywhere in it. I did following
… emp_id like ‘%_ %’ — dint work.
any idea?
Hi Seemit,
Try this one, most of the time we do have padding after the value and that is the reason LIKE might not give you what you want. Using TRIM functions removes unwanted paddings.
SELECT ‘This is Teradata’ COL1
WHERE RTRIM(LTRIM(col1)) LIKE ‘% %’
The above would return
COL1
This is Teradata
SELECT ‘Teradata’ COL1
WHERE RTRIM(LTRIM(col1)) LIKE ‘% %’
The above would NOT return anything. Following example with full syntax.
SELECT emp_id
FROM [table_name]
WHERE RTRIM(LTRIM([emp_id])) LIKE ‘% %’
Hope this helps.
Popular Articles