string manipulation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

string manipulation

Author

Topic

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-10-28 : 04:20:25

i have here a sample column with records.Document_filename is the fieldname.


DOCUMENT_FILENAME

Behavioral Science.txt
NULL
NULL
C:\Documents and Settings\Desktop\in another lifetime.txt
NULL
Go to My Computer.doc
C:\Documents and Settings\Desktop\ACE.Marie Quimpo.doc
C:\Documents and Settings\Desktop\Macromedia_Fireworks_8.txt

i want to generate a result where i can get the filename only.
this is the result that i want to have.


DOCUMENT_FILENAME

Behavioral Science.txt
NULL
NULL
in another lifetime.txt
Go to My Computer.doc
NULL
ACE.Marie Quimpo
Macromedia_Fireworks_8.txt

thnx for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 04:35:21

[code]SELECT RIGHT(DOCUMENT_FILENAME,CASE WHEN CHARINDEX('\',REVERSE(DOCUMENT_FILENAME))>0 THEN CHARINDEX('\',REVERSE(DOCUMENT_FILENAME))-1 ELSE LEN(DOCUMENT_FILENAME) END) FROM Table[/code]

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-28 : 04:49:13

[code]DECLARE @table table (
ID int IDENTITY(1, 1),
Document_filename varchar(100)
)

INSERT INTO @table (Document_filename)
SELECT 'Behavioral Science.txt' UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 'C:\Documents and Settings\Desktop\in another lifetime.txt' UNION ALL
SELECT NULL UNION ALL
SELECT 'Go to My Computer.doc' UNION ALL
SELECT 'C:\Documents and Settings\Desktop\ACE.Marie Quimpo.doc' UNION ALL
SELECT 'C:\Documents and Settings\Desktop\Macromedia_Fireworks_8.txt'

SELECT Document_filename = CASE WHEN ISNULL(CHARINDEX('\', Document_filename), 0) > 0
THEN REVERSE(LEFT(REVERSE(Document_filename), CHARINDEX('\', REVERSE(Document_filename))-1))
ELSE Document_filename
END
FROM @table[/code]

- Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-10-28 : 04:50:17

Ugh...I'm slow...

- Lumbago

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-10-28 : 04:53:56

thnx so much :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 05:28:29

quote:
Originally posted by sign_seventh

thnx so much :)

cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 06:25:02

REVERSE(LEFT(REVERSE(Document_filename), CHARINDEX('\', REVERSE(Document_filename))-1))

can be

RIGHT(Document_filename, CHARINDEX('\', REVERSE(Document_filename))-1)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 06:39:45

quote:
Originally posted by madhivanan

REVERSE(LEFT(REVERSE(Document_filename), CHARINDEX('\', REVERSE(Document_filename))-1))

can be

RIGHT(Document_filename, CHARINDEX('\', REVERSE(Document_filename))-1)

Madhivanan

Failing to plan is Planning to fail

which is same as what i posted

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 08:45:24

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

REVERSE(LEFT(REVERSE(Document_filename), CHARINDEX('\', REVERSE(Document_filename))-1))

can be

RIGHT(Document_filename, CHARINDEX('\', REVERSE(Document_filename))-1)

Madhivanan

Failing to plan is Planning to fail

which is same as what i posted

Thanks. I didnt see it properly

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 11:11:34

No problem Madhi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources