Removing all between [and ]

Hi All,
Does anyone know of a way of removing everything between, and including two
given characters in string using TSQL.
e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
text, how can I make this 'Sample Text'
I know this is something perhaps best done at application level, but in this
case I need to do this in the data, before it reaches the app.
Thanks!
Simon.lookup charindex() and substring() in bol
Simon Harris wrote:
> Hi All,
> Does anyone know of a way of removing everything between, and including tw
o
> given characters in string using TSQL.
> e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
> text, how can I make this 'Sample Text'
> I know this is something perhaps best done at application level, but in th
is
> case I need to do this in the data, before it reaches the app.
> Thanks!
> Simon.
>|||If you are using SQL Server 2005, you could do this with
a .NET UDF and Regular Expressions:
http://www.eggheadcafe.com/articles...5_clr_regex.asp
Robbe Morris - 2004/2005 Microsoft MVP C#
http://www.eggheadcafe.com/forums/merit.asp
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:%23HkAACQAGHA.312@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> Does anyone know of a way of removing everything between, and including
> two given characters in string using TSQL.
> e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
> text, how can I make this 'Sample Text'
> I know this is something perhaps best done at application level, but in
> this case I need to do this in the data, before it reaches the app.
> Thanks!
> Simon.
>|||hi Simon,
Try this out
select substring('Sample Text[12345]',1,charindex('[','Sample
Text[12345]',1)-1) as Result
"Simon Harris" wrote:

> Hi All,
> Does anyone know of a way of removing everything between, and including tw
o
> given characters in string using TSQL.
> e.g. If my result set returns 'Sample Text [12345]' where 12345 is unknown
> text, how can I make this 'Sample Text'
> I know this is something perhaps best done at application level, but in th
is
> case I need to do this in the data, before it reaches the app.
> Thanks!
> Simon.
>
>|||Hi Simon ,
What Manish asked will work but fails for data without any '['
Try This
Select Substring('sample text [12345]', 1, Case When
CharIndex('[','sample text [12345]') > 0 Then CharIndex('[','sample
text [12345]') - 1 Else Len('sample text [12345]') End )
With Warm regards
Jatinder Singh|||Jatinder, your timing could not have been better...I've been trying to work
out what was wrong!! :)
Thanks!!...and to the other guys that replied.
Simon.
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1134646198.240311.227770@.g44g2000cwa.googlegroups.com...
> Hi Simon ,
> What Manish asked will work but fails for data without any '['
> Try This
> Select Substring('sample text [12345]', 1, Case When
> CharIndex('[','sample text [12345]') > 0 Then CharIndex('[','sample
> text [12345]') - 1 Else Len('sample text [12345]') End )
> With Warm regards
> Jatinder Singh
>