Menu
In the ETL process, I receive a varchar field, and the length (of the value) is changed from row to row. I need to keep 5 symbols from the right side of the string. It means that I need to cut the left side but I can't, due to the unknown length.
I've tried the select substring('24:15:11',4, 5), but it doesn't help me, the string could be '2019-05-01 22:15:11'.
sql:
select substring('24:15:11',4, 5)
expected:
15:11
956
The TRIM() function removes the space character OR other specified characters from the start or end of a string. By default, the TRIM() function removes leading and trailing spaces from a string. Note: Also look at the LTRIM() and RTRIM() functions.
We can remove part of the string using REPLACE() function. We can use this function if we know the exact character of the string to remove. REMOVE(): This function replaces all occurrences of a substring within a new substring.
The SUBSTRING() function extracts some characters from a string.
The STRING_SPLIT(string, separator) function in SQL Server splits the string in the first argument by the separator in the second argument. To split a sentence into words, specify the sentence as the first argument of the STRING_SPLIT() function and ' ' as the second argument.
You can use substr. Negative starting position is interpreted as being relative to the end of the string.
select substr('24:15:11', -5)
You can use length() to determine the 2nd argument of substr():
select substr('24:15:11', length('24:15:11') - 4, 5)
or simply:
select substr('24:15:11', length('24:15:11') - 4)
Read about preosto's string functions.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
