Hi,
Don't worry about the vars, they are defined,
the following line give me an err of "Incorrect syntax near '.'."
Goal: to rename nonstardard column name.
EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')',
'COLUMN';
Thanks. 6 3011
Doug Baroter (qw********@boxfrog.com) writes: Don't worry about the vars, they are defined, the following line give me an err of "Incorrect syntax near '.'." Goal: to rename nonstardard column name.
EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')', 'COLUMN';
You can only pass constants and variables as parameters to stored procedures.
You cannot pass an expression as a parameter, but you must put everything
in variables.
Furthermore, replace() only handles fixed strings, and does not have
any capacbilities to find patterns.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland,
Thanks for the quick response. Please my further question below.
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>... Doug Baroter (qw********@boxfrog.com) writes: Don't worry about the vars, they are defined, the following line give me an err of "Incorrect syntax near '.'." Goal: to rename nonstardard column name.
EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%[^A-Za-z0-9_#$@]%','')', 'COLUMN'; You can only pass constants and variables as parameters to stored procedures. You cannot pass an expression as a parameter, but you must put everything in variables.
Are you saying I should do something like
set @tbuffer = '''+@tbuffer+''';
set @cbuffer = '''+@cbuffer+''';
and then
EXEC sp_rename @tbuffer+'.['+@cbuffer+']' ... Furthermore, replace() only handles fixed strings, and does not have any capacbilities to find patterns.
Since replace can't do the job for this case, what other option do I
have to remove the non-standard character(s), I've looked at charindex
and stuff function, could they fit in here or a better way to do it?
Doug Baroter (qw********@boxfrog.com) writes: Are you saying I should do something like set @tbuffer = '''+@tbuffer+'''; set @cbuffer = '''+@cbuffer+'''; and then EXEC sp_rename @tbuffer+'.['+@cbuffer+']' ...
Rather:
SELECT @old_name = 'tbl.' + <whatever>
SELECT @new_name = <whichever>
EXEC sp_rename @old_name, @new_name, 'column'
Since replace can't do the job for this case, what other option do I have to remove the non-standard character(s), I've looked at charindex and stuff function, could they fit in here or a better way to do it?
Unfortunately, SQL is poor for this task. You are probably better off
if you write some program in Perl, Visual Basic, VBscript, C or whatever
is your favourite client language.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Thanks, Erland, pls see below.
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn********************@127.0.0.1>... Doug Baroter (qw********@boxfrog.com) writes: Are you saying I should do something like set @tbuffer = '''+@tbuffer+'''; set @cbuffer = '''+@cbuffer+'''; and then EXEC sp_rename @tbuffer+'.['+@cbuffer+']' ... Rather:
SELECT @old_name = 'tbl.' + <whatever> SELECT @new_name = <whichever> EXEC sp_rename @old_name, @new_name, 'column'
Unfortunately the tbl name is dynamically determined. But as you
recommended below, I just use a non-SQL language to take care of the
whole problem except one minor one, that is, I haven't got the RegExp
part fully completed. Since replace can't do the job for this case, what other option do I have to remove the non-standard character(s), I've looked at charindex and stuff function, could they fit in here or a better way to do it?
Unfortunately, SQL is poor for this task. You are probably better off if you write some program in Perl, Visual Basic, VBscript, C or whatever is your favourite client language.
Doug Baroter (qw********@boxfrog.com) writes: SELECT @old_name = 'tbl.' + <whatever> SELECT @new_name = <whichever> EXEC sp_rename @old_name, @new_name, 'column'
Unfortunately the tbl name is dynamically determined.
SELECT @old_name = @tbl + '.' + @column
The point is that when you come to sp_rename you must have a single
value.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
OK. Thanks.
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>... Doug Baroter (qw********@boxfrog.com) writes: SELECT @old_name = 'tbl.' + <whatever> SELECT @new_name = <whichever> EXEC sp_rename @old_name, @new_name, 'column'
Unfortunately the tbl name is dynamically determined.
SELECT @old_name = @tbl + '.' + @column
The point is that when you come to sp_rename you must have a single value. This discussion thread is closed Replies have been disabled for this discussion. Similar topics
5 posts
views
Thread by sinister |
last post: by
|
11 posts
views
Thread by Jakanapes |
last post: by
|
4 posts
views
Thread by sankofa |
last post: by
|
5 posts
views
Thread by Joel |
last post: by
|
4 posts
views
Thread by Greg |
last post: by
|
2 posts
views
Thread by claus.hirth |
last post: by
|
3 posts
views
Thread by Jason |
last post: by
|
4 posts
views
Thread by Justin Fancy |
last post: by
|
2 posts
views
Thread by Reporter |
last post: by
| | | | | | | | | | |