Return everything from the right of " : "

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)

Return everything from the right of " : "

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-11-10 : 03:40:04

Hi, I need help please!

I need to return everything from the right of " : " string.
String = Test : New Description
Return = New Description

I thought of right & substring, the problem is the string varies and will change.
eg: Test New : New Description Test

Please Assist!

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-10 : 03:41:53

SELECT Col1, LTRIM(SUBSTRING(Col1, CHARINDEX(':', Col1) + 1))
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 04:00:34

[code]SELECT Col1, LTRIM(RIGHT(Col1, LEN(Col1)-CHARINDEX(':', Col1)))
FROM Table1[/code]

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-11-10 : 04:07:37

Thank You - Great Stuff

Select rtrim(right(MyText,len(mytext)-charindex(' : ',MyText,1))) as MyResult

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 04:13:37

quote:
Originally posted by Peso

SELECT Col1, LTRIM(SUBSTRING(Col1, CHARINDEX(':', Col1) + 1,LEN(Col1)))
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-10 : 04:47:16

OR

SELECT Col1, PARSENAME(REPLACE(Col1,':','.'),1)
FROM Table1

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-10 : 05:03:41

quote:
Originally posted by Peso

SELECT Col1, LTRIM(SUBSTRING(Col1, CHARINDEX(':', Col1) + 1))
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

SELECT Col1, LTRIM(SUBSTRING(Col1, CHARINDEX(':', Col1) + 1,,LEN(Col1)))
FROM Table1

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-10 : 05:12:41

quote:
Originally posted by madhivanan

quote:
Originally posted by Peso

SELECT Col1, LTRIM(SUBSTRING(Col1, CHARINDEX(':', Col1) + 1))
FROM Table1

E 12°55'05.63"
N 56°04'39.26"
SELECT Col1, LTRIM(SUBSTRING(Col1, CHARINDEX(':', Col1) + 1,,LEN(Col1)))
FROM Table1

Madhivanan

Failing to plan is Planning to fail