Preface
The Transformation Language
Constants
Operators
Variables
- Built-in Variables
  - SYSDATE
    - Example
- Local Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Character Functions
  - Complex Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ANY
- ARRAY
- ASCII
- AVG
- CAST
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COLLECT_LIST
- COLLECT_MAP
- COMPRESS
- CONCAT
- CONCAT_ARRAY
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CREATE_TIMESTAMP_TZ
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- EXTRACT_STRUCT
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GET_TIMEZONE
- GET_TIMESTAMP
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAP
- MAP_FROM_ARRAYS
- MAP_KEYS
- MAP_VALUES
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PARSE_JSON
- PARSE_XML
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- RESPEC
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SIGN
- SIN
- SINH
- SIZE
- SOUNDEX
- SQL_LIKE
- SQRT
- STDDEV
- STRUCT
- STRUCT_AS
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_DECIMAL38
- TO_FLOAT
- TO_INTEGER
- TO_TIMESTAMP_TZ
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID4
- UUID_UNPARSE
- VARIANCE

Transformation Language Reference

10.5
- 10.5.9
- 10.5.8
- 10.5.7
- 10.5.6
- 10.5.4
- 10.5.1

Back Next

SQL_LIKE

Returns whether a value matches a regular expression pattern. This lets you validate date patterns, such as IDs, telephone numbers, postal codes, and state names.

Syntax

SQL_LIKE(subject, pattern, escape character)

The following table describes the arguments for this command:

Argument	Required/Optional	Description
subject	Required	String data type. Passes the value you want to match against the regular expression. Enclose the value in single quotation marks.
pattern	Required	String data type. Regular expression that you want to match. Enclose the pattern in single quotation marks.
escape character	Optional	String data type. The SQL_LIKE function supports the percentage sign (%) and underscore (_) as escape characters. Enclose the escape character in single quotation marks.

Return Value

TRUE if the data matches the pattern.

FALSE if the data does not match the pattern.

NULL if the input is a null value or if the pattern is NULL.

Example

You might use SQL_LIKE in an expression to find names that match a pattern. For example, the following expression matches names against the pattern "A_#%" with the escape character '#':

SQL_LIKE(ENAME, 'A_#%', '#')

ENAME	Value
SMITH	FALSE
AX%	TRUE
MILLER	FALSE
A%	FALSE
JONES	FALSE
BLAKE	FALSE
A%l	FALSE

Functions

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal

0 COMMENTS

We’d like to hear from you! Log in to comment.

Khoi Nguyen - November 12, 2024

I copy and paste the example above to my filter. I replace ENAME with my data element Name. It gives me the same error whenever I try SQL_LIKE command

TE_7002 Transformation stopped due to a fatal error in the mapping. The expression [SQL_LIKE(Name, 'A_#%', '#')] contains the following errors [<> [SQL_LIKE(Name, 'A_#%', '#')]: : invalid function reference ... >>>>SQL_LIKE(Name, 'A_#%', '#')<<<<].

Note: I validate and ger the message 'The expression is valid' before I run the mapping.

Please test the SQL_LIKE command again to make sure it works.

Informatica Documentation Team - November 13, 2024

Hi Khoi Nguyen,

We’re working to address your comments and will get back to you.

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

SQL_LIKE

SQL_LIKE

Syntax

Return Value

Example