find characters from the end of a string

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 2000 Forums

Transact-SQL (2000)

find characters from the end of a string

Author

Topic

KimJ
Starting Member

38 Posts

Posted - 2003-08-25 : 16:33:42

Hi-

I'm working on a database that logs users print jobs. The data is going to be imported from Windows 2000 application logs. The field that contains the # of copies printed is varchar and is contained at the end of the string after a ':'. I'm trying to figure out how to search backwards in the string to come to the first occurence of the colon and get the rest of the characters after that. I can't do it from the start of the string because the number of colons in the string will vary.

What's the best way to handle this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-25 : 17:28:15

Here ya go:


CREATE TABLE Table1
(
Column1 VARCHAR(50) NOT NULL
)

INSERT INTO Table1 VALUES('Tara:42')
INSERT INTO Table1 VALUES('SomeString:1')
INSERT INTO Table1 VALUES('Hello:1564')

SELECT RIGHT(Column1, CHARINDEX(':', REVERSE(Column1)) - 1)
FROM Table1

DROP TABLE Table1

Tara

KimJ
Starting Member

38 Posts

Posted - 2003-08-25 : 19:25:17

Excellent!

Thank you...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources