Calling SQL Gurus - Using delimiters in SQL

Soldato
Joined
11 Feb 2004
Posts
4,532
Location
Surrey, UK
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?
 
use LOCATE(needle,haystack,start)

my guess would be something like:

Code:
SUBSTRING(mrDESCRIPTION, 24, LOCATE(' ',mrDESCRIPTION,24))
 
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!
 
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!
 
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
 
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
 
Back
Top Bottom