SELECTING Only Part of nvarchar SQL Server 2005

Question

Hello, I'm transferring data from one database to another (upgrading a system) and have a quick question.

In the source database (the one currently in use), I have a column that stores urls using absolute paths. In my new database, I am using relative paths. How can I trim out the absolute portion of the paths on the transfer (I'm using INSERT INTO), leaving only the file name?

Thanks! Jack

Answer 1

You could try something like

DECLARE @Table TABLE(
        Val VARCHAR(50)
)

INSERT INTO @Table SELECT '\\test\tada.xml'
INSERT INTO @Table SELECT 'tada1.xml'

SELECT  Val,
        CASE WHEN CHARINDEX('\',Val,0) > 0 THEN SUBSTRING(Val,LEN(Val) - (CHARINDEX('\',REVERSE(Val),0) - 1) + 1, CHARINDEX('\',REVERSE(Val),0)) ELSE Val END
FROM    @Table

Alternatively you could create a CLR scalar function in C# that should make this a bit easier for you.

Have a look at How to: Create and Run a CLR SQL Server User-Defined Function

Answer 2

If it's the same amount of characters in the absolute path for every file you can just use SUBSTRING. This is not the most maintainable solution, but for a one time query you should be ok.

Example.

DECLARE @filename varchar(50)
SET @filename = 'example.com/audiofiles/myaudio.mp3'

INSERT INTO newtable
(file_name)
VALUES
(SUBSTRING(@filename, 24, LEN(@filename) - 23))