TRIM() , REVERSE() and STRPOS() - learn with CK Connect Studio

The TRIM() function cleans your data. It’s very easy. When specifying strings, you can specify characters and strings. It deletes every occurrence of that character.

The example shows how to clean the order ID:

SELECT id,
       order_id,
       TRIM(order_id, '&') AS order_id_trimmed
FROM `orders.orders`;

Order id, character, and itself are some of the arguments in the function. There is a mysterious rationale for it to appear after order id. The data is now looked at after it has been cleaned.

id	order_id	order_id_trimmed
1	&&&4592022070101&&&	4592022070101
2	&&&4592022070102&&&	4592022070102
3	&&&4922022070101&&&	4592022070101
4	&&&4922022071201ć&&	4922022071201ć
5	&&&4822022071501&&&	4822022071501

REVERSE()

This is a function that may be useful. The string is nullified so that the last character becomes the first, the second is the second, and so on and so forth.

It can be used if processing ID numbers are entered in reverse order. The ID numbers indicate that the first three digits of the user ID number, the order date, and the last two digits are the control numbers. Whenever a new system ID is created, it must be entered in reverse order. The REVERSE() function can reverse the user ID number sequence.

SELECT id,
       order_id,
       REVERSE(order_id) AS order_id_reversed
FROM `orders.orders`;

The column I want to reverse is the only thing I have to say in the function. The result can be found here:

id	order_id	order_id_reversed
1	&&&4592022070101&&&	&&&1010702202954&&&
2	&&&4592022070102&&&	&&&2010702202954&&&
3	&&&4592022070101&&&	&&&1010702202294&&&
4	&&&4922022071201ć&&	&&ć1021702202294&&&
5	&&&4822022071501&&&	&&&1051702202284&&&

STRPOS()

When you want to find out the character position of a specific substring inside a given text, this is the function you want to use. This helps when, for example, you’re writing a batch of email templates for customers and need to determine the position of “@“

SELECT id,
       customer,
       email,
       STRPOS(email, '@') AS at_position
FROM `orders.orders`;

The first argument of a function is a string or a column value to search. Then you state the substring you’re looking for. In this case the function returns a positive integer, the position of the rightmost character.

id	customer	email	at_position
1	Jones & Son, Inc.	[email protected]	8
2	Jones & Son, Inc.	[email protected]	8
3	Stavros GmbH	[email protected]	9
4	Hendricks, Hendricks, Hoekstra	[email protected]	6
5	Mckenna & Partners	seckenna.com	7

In the third test, the result is as follows, using the first email, “s.jones@jones“. It has seven characters in front of @, so that’s at position eight, which is precisely what the output shows.