Help with substring select - 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 2000 Forums

Transact-SQL (2000)

Help with substring select

Author

Topic

newguy
Starting Member

1 Post

Posted - 2007-09-27 : 17:42:51

I need to grab everything to the right of a ":" in a query. The data looks like this.

Tom(Server):Testing
Andrew(looptydo001):Inc_Pro
andyanders(news):sqlteamrocks

I need the results of the query to be

Testing
Inc_Pro
sqlteamrocks

Because all the data is variable length on the left I cannot do a ltrim. (at least not in any way that I know of). Any help is greatly appreciated.

Thanks!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-27 : 17:48:19

SELECT Right(column, len(column) - charindex(':',column, 1))

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 20:10:21

You need DATALENGTH(), instead of LEN(), otherwise trailing spaces will spoil your day

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-28 : 02:41:20

quote:
Originally posted by Kristen

You need DATALENGTH(), instead of LEN(), otherwise trailing spaces will spoil your day if the datatype of the column is CHAR

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-28 : 03:09:29

Nope, will spoil your day with VARCHAR too:


DECLARE @TEMP TABLE
(
	MyID		int IDENTITY(1,1) NOT NULL,
	MyString	varchar(8000)
)

INSERT INTO @TEMP
SELECT 'Tom(Server):Testing' UNION ALL
SELECT 'Tom(Server):Testing '

SELECT	MyID,
	Right(MyString, len(MyString) - charindex(':',MyString, 1)) + '[',
	Right(MyString, DATALENGTH(MyString) - charindex(':',MyString, 1)) + '['
FROM	@TEMP

MyID                                                    
----------- --------------------- --------------------- 
1           Testing[              Testing[
2           esting [              Testing [

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 03:15:03

SELECT dbo.fnParseString(1, ':', Col1)
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources