STRING FUNCTION

In Snowflake, a cloud-based data warehousing platform, you can work with

strings using SQL syntax. Snowflake supports a variety of string manipulation
and processing functions.

----CREATE TABLE

CREATE OR REPLACE TABLE AGENTS

(
AGENT_CODE CHAR(6) NOT NULL PRIMARY KEY,
AGENT_NAME CHAR(40) ,
WORKING_AREA CHAR(35),
COMMISSION NUMBER(10,2) DEFAULT 0.05,
PHONE_NO CHAR(15),
COUNTRY VARCHAR2(25)
);

1
---INSERT RECORDS

INSERT INTO AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore',0.15,'077-

25814763', '');
INSERT INTO AGENTS VALUES ('A003', 'Alex ', 'London', '0.13', '075-12458969',
'');
INSERT INTO AGENTS VALUES ('A008', 'Alford', 'New York', '0.12', '044-
25874365', '');
INSERT INTO AGENTS VALUES ('A011', 'Ravi Kumar', 'Bangalore', '0.15', '077-
45625874', '');
INSERT INTO AGENTS VALUES ('A010', 'Santakumar', 'Chennai', '0.14', '007-
22388644', '');
INSERT INTO AGENTS VALUES ('A012', 'Lucida', 'San Jose', '0.12', '044-
52981425', '');
INSERT INTO AGENTS VALUES ('A005', 'Anderson', 'Brisban', '0.13', '045-
21447739', '');
INSERT INTO AGENTS VALUES ('A001', 'Subbarao', 'Bangalore', '0.14', '077-
12346674', '');
INSERT INTO AGENTS VALUES ('A002', 'Mukesh', 'Mumbai', '0.11', '029-
12358964', '');
INSERT INTO AGENTS VALUES ('A006', 'McDen', 'London', '0.15', '078-
22255588', '');
INSERT INTO AGENTS VALUES ('A004', 'Ivan', 'Torento', '0.15', '008-22544166',
'');
INSERT INTO AGENTS VALUES ('A009', 'Benjamin', 'Hampshair', '0.11', '008-
22536178', '');

INSERT INTO AGENTS(AGENT_CODE,AGENT_NAME,WORKING_AREA)

VALUES ('A110', 'Anand', 'Germany');

2
 A. CONCAT , ||

Concatenates one or more strings, or concatenates one or more binary

values. If any of the values is null, the result is also null.
The | | operator provides alternative syntax for CONCAT and requires at
least two arguments.

EXAMPLE:-

1. SELECT CONCAT('ANAND','JHA') AS FULL_NAME;

2. WHEN WE REQUIRE SPACE BETWEEN BOTH THE NAME .

WILL DO AS FOLLOW: SPACE IS BEFORE SECOND LETTER.

SELECT CONCAT(‘ANAND’, ‘ JHA’) AS FULL_NAME;

USING “ | | “

3. SELECT ‘ANAND’ || ‘JHA’ AS FULL_NAME;

3
4. SELECT 'ANAND' || ' KUMAR' || ' JHA' AS FULL_NAME;

5. CONCANATING AGENT_CODE AND AGENT_NAME COLOUMN OF AGENT

TABLE.
SELECT CONCAT( AGENT_CODE, AGENT_NAME) AS AGENT_DETAIL
FROM AGENTS;

6. CONCATNATING TWO COLOUMN WITH SPACE IN BETWEEN.

SELECT CONCAT( AGENT_CODE, ' ' ,AGENT_NAME) AS AGENT_DETAIL
FROM AGENTS;

4
 7. CONCANATING TWO COLOUMN USING “ || ”.

SELECT AGENT_CODE || ' ' || AGENT_NAME AS AGENT_DETAIL FROM

AGENTS;

B. CONCAT_WS

Concatenates two or more strings, or concatenates two or more binary

values. If any of the values is null, the result is also null.
The CONCAT_WS operator requires at least two arguments, and uses the first
argument to separate all following arguments.

SYNTAX:-
CONCAT_WS( <separator> , <expression1> [ , <expressionN> ... ] )

EXAMPLE:-
1. SELECT CONCAT_WS('-', 'KA','India') as state_country;

5
 2. CONCATENATE TWO COLUMN AND USING SEPARATER AS ‘ - ‘ , SEPARATE TWO
COLUMN.
SELECT CONCAT_WS(‘ –‘, AGENT_CODE, AGENT_NAME) AS AGENT_DETAIL
FROM AGENTS;

C. LENGTH, LEN
We can find the length of a string using the LENGTH function.
SYNTAX:
LENGTH(<expression>)
LEN(<expression>)

EXAMPLE:
1 . SELECT LEN ('ANAND KUMAR JHA') AS LENGTH;

2. SELECT LENGTH('ANAND KUMAR JHA') AS LENGTH;

6
 D. SUBSTRING, SUBSTR

We can extract a substring from a string using the SUBSTRING/SUBSTR

function.
It returns the position of a string or binary value from the complete string,
starting with the character specified by substring_start_index. If any input is
null, null is returned.

EXAMPLE:-

1. select substring('ANAND KUMAR JHA', 1, 9) AS PARTIAL_NAME;

2. select substring('ANAND KUMAR JHA', 4, 7);

3. If we want only A from ANAND and K From KUMAR .

select substr('Anand Kumar Jha',1,1)||substr('Anand Kumar Jha',7,1) as

initial;

OR

7
select concat(substring('ANAND KUMAR JHA',1,1),substring('ANAND KUMAR
JHA',7,1)) AS partial_name;

4. This will give all the character after 3 (including 3).

select substr('ANAND JHA',3);

5. In this will count character from end .

select substring('ANAND KUMAR JHA',-7,5);

E. TRIMMING

You can remove leading and trailing spaces from a string using the TRIM
Function.
We have three type of TRIM Function.
 LTRIM
 RTRIM
 TRIM

 LTRIM:
Removes leading characters, including whitespace, from a string.

SYNTAX:
LTRIM( <expression> [ character])

EXAMPLE:
1. Remove leading ‘0’ and ‘#’ characters from a string:

select LTRIM('#0000AISHWARYA', '#0');

2. Remove leading ‘ ’ from a string:

select LTRIM(' ANAND JHA', '');

9
  RTRIM
Removes trailing characters, including whitespace, from a string.

SYNTAX:
RTRIM( <expression> [ character])

EXAMPLE:-

1. Remove trailing 0 and . characters from a string

select RTRIM ('$125.00', '0.');

2. Remove trailing * characters from a string

select RTRIM('ANAND JHA*****', '*');

10
  TRIM
Removes leading and trailing characters from a string.

SYNTAX:-
TRIM( <expression> [ character])

EXAMPLE:-

1. Remove leading and trailing ❄ and - characters from a string:

select TRIM('❄-❄ABC-❄-', '❄-') as trimmed_string;

2. Remove leading and trailing *.

SELECT TRIM('********T E S T I N G 1 2 3 4********','*') AS

TRIMMED_SPACE;

3. Remove leading and trailing *.

SELECT TRIM('********T E S T I*N*G 1 2 3 4********','*') AS

TRIMMED_SPACE;

11
 F. REVERSE

Reverses the order of characters in a string, or of bytes in a binary value.

The returned value is the same length as the input, but with the
characters/bytes in reverse order.

SYNTAX:-
REVERSE(STRING)

EXAMPLE:-

1. SELECT REVERSE('Hello, world!');

2. SELECT REVERSE('India is My Country');

12
 G. REPLACE

Removes all occurrences of a specified substring, and optionally replaces them with
another string.
The returned value is the string after all replacements have been done.

SYNTAX:-
REPLACE( <subject> , <pattern> [ , <replacement> ] )

EXAMPLE:-

1. Replace bc string in abcd with an empty string

select replace('abcd', 'bc') as replaced_nothing_just_remove;

2. Replace Whitespace with *.

select REPLACE( ' ANAND KUMAR JHA ' ,' ','*');

3. Replacing whitespace.
SELECT REPLACE(' T E S T I N G 1 2 3 4 ',' ');

13
H. REPEAT

It Builds a string by repeating the input for the specified number of

times.

SYNTAX:-
REPEAT(INPUT , NUMBER)

EXAMPLE:-

1.To repeat a string 5 times.

SELECT REPEAT(‘ANAND JHA ‘, 5);

I. LIKE

It is used to perform pattern matching within a column of text data. It

is commonly used in conjunction with the SELECT statement to
retrieve rows that match a specific pattern or substring.
The LIKE operator is particularly useful when you want to search for
records based on partial information or when you need to perform
wildcard searches.

It’s a case sensitive.

SYNTAX:-

SELECT column1, column2, ...

FROM table_name
WHERE column_name LIKE pattern;

14
 There are two common wildcard characters used with the LIKE
operator:

1. % (percent sign): This wildcard character matches any sequence

of characters (including zero characters).

2 _ (underscore): This wildcard character matches a single

character. You can use it to find patterns with specific characters in
specific positions.

EXAMPLE:-

1. If you want to find all rows where the agent_name column starts with
"An" .

select * from AGENTS

where AGENT_NAME LIKE 'An%';

2. If you want to find all rows where the agent_name contain “ar” in
between .

Select * from AGENT

Where AGENT_NAME LIKE '%ar%';

15
3. If you want to find all rows where the agent_name second letter
should be “l” .
Select * from AGENTS
Where AGENT_NAME LIKE ‘_l%’;

4. If you want to find all rows where the agent_name third letter should
be “a” .
Select * from AGENTS
Where AGENT_NAME LIKE ' a%';

5. If you want to find all rows where the agent_name last letter should
be “r” .
Select * from AGENTS
Where AGENT_NAME LIKE '%r';

16
6. If you want to find all rows where the agent_name last second letter
should be “a” .
Select * from AGENTS
Where AGENT_NAME LIKE '%a_';

7. If you want to find all rows where the agent_name second last and
third letter should be “a” and “m” respectively.
Select * from AGENTS
Where AGENT_NAME LIKE '%ma_';

8. If you want to find all rows where the agent_code contain “01” in
between.
select * from AGENTS
where AGENT_CODE LIKE '%01%';

17
 J. SPLIT

Split function is used to split a string into multiple rows based on a delimiter. This
function is especially useful when you want to split a string into multiple values and
treat them as separate rows in your query.

SYNTAX:-
SELECT SPLIT(COLOUMN | STRING, DELIMETER)
FROM TABLE_NAME;

RETURN TYPE:- ARRAY

EXAMPLE:-

--CREATE TABLE
create or replace table aj_persons
(
NAME CHAR(10),
CHILDREN VARCHAR(30)
);

--INSERT VALUE
INSERT INTO AJ_PERSONS
VALUES('Mark','Marky,Mark Jr,Maria'),('John','Johnny,Jane');

18
SELECT * FROM AJ_PERSONS;

1. Split the children column.

SELECT SPLIT(CHILDREN,',') FROM AJ_persons;

2. Split the custom agent_details column.

SELECT split(AGENT_DETAILS, '-')

FROM (
SELECT *,concat(AGENT_CODE, '-', AGENT_NAME) AS
agent_details
from agents );

19
 K. SPLIT_PART

Splits a given string at a specified character and returns the requested part.
If any parameter is NULL, NULL is returned.

SYNTAX:-
SELECT SPLIT_PART( STRING, DELIMETER,PART NUMBER)

EXAMPLE:-

1. Splitting the first 1st part and 2nd part of IP which is separated by delimiter “.”.

select split_part('11.22.33', '.', 1) as first_part ,

split_part('11.22.33', '.', 2) as sec_part;

2. select split_part('aaa--bbb-BBB--ccc', '--',1) as first_part;

3. select split_part('aaa--bbb-BBB--ccc', '--',3) as third_part;

20
 L. LOWER

It returns the input string (expr) with all characters converted to

lowercase.

SYNTAX :- LOWER(exp)

EXAMPLE:-

SELECT LOWER('ANAND KUMAR JHA') AS LOWER_CASE;

M. UPPER
It Returns the input string (expr) with all characters converted to
lowercase.

SYNTAX :- UPPER(exp)

EXAMPLE:-

SELECT UPPER(‘anand kumar jha’) AS UPPER_CASE;

21
 N. INITCAP

It returns the input string exp with the first letter of each word in
uppercase and the subsequent letters in lowercase.

SYNTAX:- INITCAP(EXP)

EXAMPLE:-

SELECT INITCAP(‘ INDIA Is MY cOUntRY’) AS TITLE;

22