o365spo o365spo - 1 year ago 77
SQL Question

SQL text before then Nth match?

Using SQL I'd like to return all text before the 3rd forward slash in a column



would return:


Any quick and dirty way to do this in SQL (specifically MS T-SQL under MS SQL 2005+) ?

Answer Source

Since you said "quick and dirty", I'm assuming that this very quick and very dirty solution won't receive a bunch of down votes. The SQL below uses multiple SUBSTRING() functions to find the third slash:

SET @str = '/one/two/three/whatever/testing'
SELECT SUBSTRING(@str, 0, CHARINDEX('/', @str, CHARINDEX('/', @str, CHARINDEX('/', @str, CHARINDEX('/', @str, 0) + 1) + 1) + 1))

You can see a working example here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download