Regular Expression?

[NickName]

I need to remove all the [ and ] in every sql stmt where they are used
to close encircling numeric value, fyi, all these sql stmt are
converted from an Access db. For instance,
select *
from XYZtbl
where fieldA = [1] or fieldA = [2] or fieldA = [3]

to be
select *
from XYZtbl
where fieldA = 1 or fieldA = 2 or fieldA = 3

-- and of course I'd have written in as follows, but that's not
-- the point
select *
from XYZtbl
where fieldA IN (1,2,3)

Thanks.

[John Bell]

Hi

If you are generating these queries dynamically they should be removed in
the code that generates them.

You can not change the query once it has been sent to the server unless you
write your own driver, therefore you will have to change the source. You may
want to create stored procedures from these queries rather than use ad-hoc
queries.

John

"NickName" <da****@rock.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I need to remove all the [ and ] in every sql stmt where they are used
to close encircling numeric value, fyi, all these sql stmt are
converted from an Access db. For instance,
select *
from XYZtbl
where fieldA = [1] or fieldA = [2] or fieldA = [3]

to be
select *
from XYZtbl
where fieldA = 1 or fieldA = 2 or fieldA = 3

-- and of course I'd have written in as follows, but that's not
-- the point
select *
from XYZtbl
where fieldA IN (1,2,3)

Thanks.

[Hugo Kornelis]

On 5 Sep 2005 18:38:58 -0700, NickName wrote:

I need to remove all the [ and ] in every sql stmt where they are used
to close encircling numeric value, fyi, all these sql stmt are
converted from an Access db. For instance,
select *
from XYZtbl
where fieldA = [1] or fieldA = [2] or fieldA = [3]

to be
select *
from XYZtbl
where fieldA = 1 or fieldA = 2 or fieldA = 3

-- and of course I'd have written in as follows, but that's not
-- the point
select *
from XYZtbl
where fieldA IN (1,2,3)

Thanks.

Hi NickName,

Where are these queries stored?
SQL Server doesn't store actual queries - it accepts them, executes
them, then returns you the results.
On the other hand, the code for stored procedures, user-defined
functions and triggers is stored in SQL Server.

If you have your queries stored in files on your computer, check
www.tucows.com for a freeware or shareware GREP-like utility to quickly
make the change for you (but beware that you specify the regular
expression patterns in a way to prevent unwanted side-effects!!)

If this is code in stored procedures etc, then there is no easy way to
change it all at once. You'll either have to change the procedures one
by one (which is a good opportunity to bring them all in some change
control system), or investigate what went wrong on the conversion from
Access, fix that problem, then re-convert.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[NickName]

Hi Hugo,

These are 'saved' queries in Access I'm in the process of converting
them to either View or SP, and obviously SQL Server would accept
something like
select *
from XYZtbl
where fieldA = [1] or fieldA = [2] or fieldA = [3]

I thought about GREP but wanted to check if there's other option.

On Access conversion, it convert queries to virtual tables.

Thanks.

Don

[NickName]

"If you are generating these queries dynamically they should be removed
in
the code that generates them."
Exactly, that's what I wanted to do in the generation/conversion
process.

"You may
want to create stored procedures from these queries rather than use
ad-hoc
queries. "
Yes, either view or sp.

Thanks.

[John Bell]

Hi

Then you should look at the search/replace functionality of a text editor or
even Query Analyser.

I have not come across this thing before in access queries, so I am not sure
why they are there.

John

"NickName" <da****@rock.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

"If you are generating these queries dynamically they should be removed
in
the code that generates them."
Exactly, that's what I wanted to do in the generation/conversion
process.
"You may
want to create stored procedures from these queries rather than use
ad-hoc
queries. "
Yes, either view or sp.

Thanks.