Yesterday, I encountered a very simple problem in the project. I need to extract the file name from a path address saved in the SQL Server database, but SQL Server has no ready-made method, finally, find this method on the Internet. The principle is to first reverse the string, retrieve the first position, extract it from the beginning, and then reverse it again.
Yesterday, I encountered a very simple problem in the project. I need to extract the file name from a path address saved in the SQL Server database, but SQL Server has no ready-made method, finally, find such a method on the Internet. The principle is to first reverse the string, retrieve the first position of the/, extract it from the beginning, and then reverse it again.
SQL statement
The Code is as follows:
Reverse (substring (reverse (Path), 1, charindex ('/', reverse (Path)-1 ))
How can I retrieve the right character of the last special character in a string, for example, 10*20*300? How can I get 300?
Use reverse with charindex.
Reverse is to put the string upside down, and then use charindex to obtain the first * position after the inversion, then use the substring function to intercept the string, and then use the reverse to put it back.
The following is an example
Declare @ str varchar (20)
Set @ str = '10*20*300'
Select reverse (substring (reverse (@ str), 1, charindex ('*', reverse (@ str)-1 ))
Result 300
The above is the SQL Server syntax.
The ORACLE database also has the REVERSE function, but the SUBSTRING should be changed to SUBSTR, And the CHARINDEX function should be changed to the INSTR function.
SELECT reverse (substr (reverse ('10*20*300 '), 1, INSTR (reverse ('10*20*300'), '*')-1 )) from dual;
Result 300
