Scrape data between brackets - [] 2

[FranS37]

I have data in a field - example below:

OUT: RECORDS FLAG, . [09098]

I need to scan this field and return the data that falls between the brackets []. Can I do that in a query?

Thanks.

 

[chamilz]

select  substring(YourColumn,2,len(YourColumn)-2)
from YourTable

 

[FranS37]

The value in the field is OUT: RECORDS FLAG, . [09098]

CODE
select substring(YourColumn,2,len(YourColumn)-2)
from YourTable

is returning UT: RECORDS FLAG, . [09098

It stripped one character from the begining and one character from the end of the string.

What I need to return is 09098 (or the data that falls between the brackets.


Thanks.

 

[MarkSweetland]

Try

select @fld = SUBSTRING(@fld, CHARINDEX('[', @fld)+1, CHARINDEX(']', @fld) - CHARINDEX('[', @fld)-1)

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[bborissov]

DECLARE @test varchar(200)
set @test ='OUT: RECORDS FLAG, . [09098]'

SELECT SUBSTRING(@test, CHARINDEX('[', @test)+1,
                        CHARINDEX('[', REVERSE(@test))-2)

Borislav Borissov
VFP9 SP2, SQL Server

 

[chamilz]

select substring(YourColumn,charindex('[',YourColumn)+1, charindex(']',YourColumn)-charindex('[',YourColumn)-1)
from YoutTable

 

[drlex]

In case the data lacks both square brackets, and modifying bborissov's suggestion:

SELECT [red][b]case when @test like '%[[]%]%' THEN [/b][/red]
SUBSTRING(@test, CHARINDEX('[', @test)+1, CHARINDEX('[', REVERSE(@test))-2) 
[red][b]ELSE '' END[/b][/red]

("[]]" to use the [ wildcard as a literal)

soi là, soi carré

 

[FranS37]

Thank you everybody, got what I needed!

 

[FranS37]

I have a new version of this request. There are two possible variations for the format of the value in this field:

1. OUT: NAME [09098]

2. IN: OFFSITE STORAGE [OFFICECODE]

Can I strip out 'OUT:' AND 'IN:' and the brackets?

So that 1. would be

NAME 09098

and 2. WOULD BE

OFFSITE STORAGE OFFICECODE

 

[IanWaterman]

YOu can use replace() togther with existing formula

Replace(Replace((SUBSTRING(@test, CHARINDEX('[', @test)+1, CHARINDEX('[', REVERSE(@test))-2)
), 'IN: ', ''), 'OUT: '. '')

There might be a more efficient way but this should work ;-)

Ian

 

[FranS37]

Well, not exactly. For example, my 1. would return

NAME 09098

Your SQL returns:

09098

Thanks!

 

[IanWaterman]

OOPS!

Try

Replace(Replace(Replace(Replace(yourfield, 'IN: ', ''), 'OUT: '. ''),'[',''),']','')

Ian

 

[FranS37]

Wow, perfect. And I can see what your're doing!

Thank you!

 

[FranS37]

Okay, once more? I tried this, but couldn't get it to work.

They don't want to see what's between the brackets, so that

2. IN: OFFSITE STORAGE [OFFICECODE]

would just return

OFFSITE STORAGE

Thanks.

 

[TheBugSlayer]

If you've looked at all the answers the other members have given you should be able to figure out how to return all text between the the colon ) and the opening square bracket ([).

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[FranS37]

Ah, if it were true.