Calling SQL Gurus - Using delimiters in SQL

Domo · 31 Oct 2007 at 10:44

I need to extract a text string from a text field in SQL 2005. Its highlighted in yellow below.

The string looks like this in the db:

<! 2007-10-10 08:09:29 jrt1130 >

I've written a view to do this:

SELECT SUBSTRING(mrDESCRIPTION, 24, 7) AS UserName, mrID, mrGENERATION, mrDESCRIPTION
FROM dbo.MASTER6_DESCRIPTIONS

The problem is - the id can be variable in length. Therefore the start position will always be 24 but I need to use the space right after the id as the delimiter.

Can I achieve this?

Sic · 31 Oct 2007 at 10:48

use LOCATE(needle,haystack,start)

my guess would be something like:

Code:

SUBSTRING(mrDESCRIPTION, 24, LOCATE(' ',mrDESCRIPTION,24))

Dj_Jestar · 31 Oct 2007 at 10:51

I'm guessing you are using MS? MySQL and PostgreSQL both support regex, but I don't know if MSSQL does.

Sic · 31 Oct 2007 at 10:55

after having had a look, it appears that there's no such thing as LOCATE in SQL2005. Not having a very good day today, sorry

teach me to assume!

Domo · 31 Oct 2007 at 11:04

Yeah its MS SQL 2005 so no regular expressions unfortunately. Sic - LOCATE is not a recognised function? I'm hoping there's an SQL alternative?

EDIT - Just seen your reply!

Sic · 31 Oct 2007 at 11:24

aHA!

try CHARINDEX - it's the same format, so replacing LOCATE with CHARINDEX should do the trick

this post was brought to you by proof that wishing hard enough can make things real

~J~ · 31 Oct 2007 at 11:24

Get the length of the field (with LEN) and if you can guarantee that the start position is always at 24, then just use:

SELECT SUBSTRING(mrDESCRIPTION, 24, LEN(mrDESCRIPTION)-24) AS UserName, mrID, mrGENERATION, mrDESCRIPTION
FROM dbo.MASTER6_DESCRIPTIONS