CODEWITHSUNDEEP
mysqlsqlsql-server
Ratan Servegar
Ratan Servegar

Reputation: 415

(SQL Server) return sub-string value after 1st occurrence of 'character'

I have a column in which contains data like 10374-0-dd.txt or 1037424444-0-dd.txt

I was currently doing - SUBSTRING(sa.FileName, 9,100) to return the file name from the 9th position that is (dd.text) but now there is an issue the starting no. of numerals vary.

How do i return the filename regardless of the number of characters in front of it?

Upvotes: 0

Views: 222

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

Use SUBSTRING_INDEX, they can get the last substring start with "-"

Sample

MariaDB []> SELECT SUBSTRING_INDEX('10374-0-dd.txt', '-', -1);
+--------------------------------------------+
| SUBSTRING_INDEX('10374-0-dd.txt', '-', -1) |
+--------------------------------------------+
| dd.txt                                     |
+--------------------------------------------+
1 row in set (0.00 sec)

MariaDB []> SELECT SUBSTRING_INDEX('10374-0--1234-56789-hallo.txt', '-', -1) ;
+-----------------------------------------------------------+
| SUBSTRING_INDEX('10374-0--1234-56789-hallo.txt', '-', -1) |
+-----------------------------------------------------------+
| hallo.txt                                                 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB []>

Upvotes: 2

Related Questions