SQL Server -- Select a string between two known strings

Question

I'm trying to insert into another column the database name from the TEXT column return by the following query, which was inserted into another table:

EXEC master..sp_readerrorlog 0,1,"Database backed up. Database:"

which returns

Database backed up. Database: databaseName, creation date(time): 2014/06/13(17:49:54), pages dumped: 197581, first LSN: 488:6298:36, last LSN: 488:6314:1, number of dump devices: 4, device information: ...

Following recomandations of other posts from here, I wrote:

SUBSTRING([TEXT], LEN(LEFT([TEXT], CHARINDEX (': ', [TEXT]))) + 1, LEN([TEXT]) - LEN(LEFT([TEXT], 
CHARINDEX (': ', [TEXT]))) - LEN(RIGHT([TEXT], LEN([TEXT]) - CHARINDEX (', ', [TEXT]))) + 1) 
as DatabseName

which returns

'DatabaseName,' keeping the comma

I've tried to alter both CHARINDEX parameter '+1' without success.

Anybody has an idea on how to get rid of that comma ?

Jason W · Accepted Answer · 2014-10-30 13:06:28Z

1

How about this line?

SUBSTRING([TEXT], CHARINDEX('Database:', [TEXT]) + 10, CHARINDEX('creation date', [TEXT]) - (CHARINDEX('Database:', [TEXT]) + 12))

answered Oct 30, 2014 at 13:06

Jason W

13.2k3 gold badges35 silver badges62 bronze badges

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

2 Comments

jpw Over a year ago

No need to search for the entire words and using large offsets, this will work just as well and is a bit shorter: SUBSTRING([TEXT],CHARINDEX(':',[TEXT])+2, CHARINDEX(',',[TEXT]) - (CHARINDEX(':',[TEXT])+2)) +1 anyway

Kukah Over a year ago

I didn't work either. A collegue of mine suggest:

replace(SUBSTRING([TEXT], LEN(LEFT([TEXT], CHARINDEX (': ', [TEXT]))) + 2, LEN([TEXT]) - LEN(LEFT([TEXT], CHARINDEX (': ', [TEXT]))) - LEN(RIGHT([TEXT], LEN([TEXT]) - CHARINDEX (', ', [TEXT]))) - 0),',','')

, which works. But I still would like to know if it's possible just using Substring and Charindex.

Fandango68 · Accepted Answer · 2019-07-18 03:59:24Z

0

There is always a function for any string manipulation out there.

Oooh look what Google found!

http://www.spatialdbadvisor.com/sql_server_blog/136/string-tokenizer-for-sql-server-2008-written-in-tsql/

:)

answered Jul 18, 2019 at 3:59

Fandango68

4,9555 gold badges50 silver badges88 bronze badges

Collectives™ on Stack Overflow

SQL Server -- Select a string between two known strings

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related