Extract string from specific character in sql

Question

In My SQL I have String like

Declare @Temp Varchar(Max) ='Pravin Gaonkar: 12 Jan 2013 11:56:21 : Hello World 1*Makarand Sawant: 12 Jan 2013 15:36:39 : Hello World 2*Makarand Sawant: 12 Jan 2013 21:21:51 : Hello World 3 *'

I have used '*' As Seprator

I want to retrieve Last String from 2nd Last * Character to Last * Character

in above example the resultant string would be

Makarand Sawant: 12 Jan 2013 21:21:51 : Hello World 3

MY Query

SELECT Reverse(Left(Reverse(@Temp), Charindex('*', Reverse(@Temp)) -1))

But it is giving me error Invalid length parameter passed to the LEFT or SUBSTRING function.

Database is SQL SERVER 2008

Do you expect * characters after the last one?

gbn
– gbn

2013-01-21 12:44:42 +00:00
Commented Jan 21, 2013 at 12:44 — gbn
– gbn, Commented Jan 21, 2013 at 12:44

gbn · Accepted Answer · 2013-01-21 12:46:46Z

2

This assumes no characters after the final * and that you have at least 2 * overall.

SELECT REVERSE(SUBSTRING(LTRIM(Reverse(@Temp)), 2, CHARINDEX('*', @Temp, 2)+1))

Personally, I would store this in separate clauses and split it in the client code. Java and .net have far better string handling than SQL Server

answered Jan 21, 2013 at 12:46

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shaggy Over a year ago

if the string is 'Thakur: 21 Jan 2013 16:14:46 : Cash Is Getting Reversals.*Namdev: 21 Jan 2013 17:41:51 : SLM call logg for ATM not coming inservice Docket No# W301211224 Cust JAGJEET *' Then its not working

bonCodigo · Accepted Answer · 2013-01-21 13:23:35Z

1

Another method: You need one * in the middle of the string (where you want to split the string) to use this code.

Select replace(Right(yourstring,Len(yourstring) - charindex('*',yourstring)),'*','')
from yourtable
;

SQLFIDDLE DEMO

Results:

NEWVALUE
Namdev: 21 Jan 2013 17:41:51 : SLM call logg for ATM not coming
 inservice Docket No# W301211224 Cust JAGJEET

edited Jan 21, 2013 at 13:23

answered Jan 21, 2013 at 13:11

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

5 Comments

bonCodigo Over a year ago

Yes that's the assumption we have taken to arrive at this solution.. There has to be at least one * in the middle to sort of simulate the split... :) As long as there's one * in middle to split the string it works, doesn't really matter if you have a * at the end. sqlfiddle.com/#!3/d97bb/1

Shaggy Over a year ago

Then What about this string --->> 'Thakur: 21 Jan 2013 11:21:27 : Rp FatalSingh: 21 Jan 2013 14:17:24 : as per custodian Akshy Ule 844681 engg working at siteNamdev: 21 Jan 2013 16:21:15 : SLM Call logg for RP problenm docket No#317402 *'

Shaggy Over a year ago

If the String has More than 2 '*' Then also it Fails

bonCodigo Over a year ago

For any efforts that I have spent so far you have even taken away the vote which was there earlier, so how do anyone ever think that further efforts will anyway be appreciated? ( if it was you who took it off....)? You can clearly see my assumption up in the answer., frankly you may put all your test cases in the question and hopefully someone will help you...

bonCodigo Over a year ago

Here is the link if you want to take a look: sqlfiddle.com/#!3/2258d/3

Collectives™ on Stack Overflow

Extract string from specific character in sql

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related