Replacing columnn name programmatically

Hi,

The following script does not return any resultset against a test db
while I know for a fact tables with letter "aaa" has columns that
contains "ccc".
What's wrong? the the inner cursor?

Thanks.

-- get all tbls with letter aaa
declare @.tbl varchar(8000)
declare tblCursor cursor for
SELECT name
FROM sysobjects
WHERE xtype = 'U'
AND name LIKE '%aaa%'

open tblCursor
fetch next from tblCursor
into @.tbl

while (@.@.fetch_status = 0)
begin

-- get all columns with letter ccc and replace it with nothing /
remove it
declare @.tbuffer varchar(4000)
declare @.cbuffer varchar(8000)

declare abnormal_cols cursor for
SELECT o.name, c.name
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
WHERE o.xtype = 'U'
AND c.name LIKE '%ccc%'
and o.id = object_id('+@.tbl')
-- ORDER BY c.name

open abnormal_cols
fetch next from abnormal_cols
into @.tbuffer,@.cbuffer

while (@.@.fetch_status = 0)
begin
-- EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%ccc%','')',
'COLUMN';
-- test
print @.tbuffer + ', ' + @.cbuffer;
fetch next from abnormal_cols
into @.tbuffer,@.cbuffer
end

close abnormal_cols
deallocate abnormal_cols;

fetch next from tblCursor
into @.tbl

end
close tblCursor
deallocate tblCursor;Hi

I can't see why there are two cursors here, try:

SELECT o.name, Replace(c.name,'ccc','') as NewName, c.name as OldName
FROM sysobjects o JOIN syscolumns c ON o.id = c.id
JOIN syscolumns a ON o.id = a.id
WHERE o.xtype = 'U'
AND c.name LIKE '%ccc%'
AND a.name LIKE '%aaa%'

John

"Doug Baroter" <qwert12345@.boxfrog.com> wrote in message
news:fc254714.0310211451.2f59f9c4@.posting.google.c om...
> Hi,
> The following script does not return any resultset against a test db
> while I know for a fact tables with letter "aaa" has columns that
> contains "ccc".
> What's wrong? the the inner cursor?
> Thanks.
>
> -- get all tbls with letter aaa
> declare @.tbl varchar(8000)
> declare tblCursor cursor for
> SELECT name
> FROM sysobjects
> WHERE xtype = 'U'
> AND name LIKE '%aaa%'
> open tblCursor
> fetch next from tblCursor
> into @.tbl
> while (@.@.fetch_status = 0)
> begin
> -- get all columns with letter ccc and replace it with nothing /
> remove it
> declare @.tbuffer varchar(4000)
> declare @.cbuffer varchar(8000)
> declare abnormal_cols cursor for
> SELECT o.name, c.name
> FROM sysobjects o
> JOIN syscolumns c ON o.id = c.id
> WHERE o.xtype = 'U'
> AND c.name LIKE '%ccc%'
> and o.id = object_id('+@.tbl')
> -- ORDER BY c.name
> open abnormal_cols
> fetch next from abnormal_cols
> into @.tbuffer,@.cbuffer
> while (@.@.fetch_status = 0)
> begin
> -- EXEC sp_rename
'+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%ccc%','')',
> 'COLUMN';
> -- test
> print @.tbuffer + ', ' + @.cbuffer;
> fetch next from abnormal_cols
> into @.tbuffer,@.cbuffer
> end
> close abnormal_cols
> deallocate abnormal_cols;
> fetch next from tblCursor
> into @.tbl
> end
> close tblCursor
> deallocate tblCursor;

Replace multiple spaces in varchar

How can I replace multiple spaces in a varchar string?
Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
I want to find all occurrences of 2 or more spaces and replace with 1 space.
My reporting application can't handle this.
Thankstry this
replace( 'ABC DEF GHI', ' ',' ')
"Terri" <terri@.cybernets.com> wrote in message
news:d3ot2k$p63$1@.reader2.nmix.net...
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1
> space.
> My reporting application can't handle this.
> Thanks
>|||--One possibility:
SELECT REPLACE(REPLACE('ABC DEF GHI JKL', ' ', ' '), ' ', ' ')
Handles up to 4 spaces
SELECT REPLACE(REPLACE(REPLACE('ABC DEF GHI JKL', ' ', '
'), ' ', ' '), ' ', ' ')
Handles up to 8 spaces
I know this works on my setup but maybe an expert will see a problem with
this.
Another (probably slower but will handle any number of spaces)
would be to build a function to handle this and integrate this logic:
DECLARE @.str VARCHAR(50)
SET @.str = 'ABC DEF GHI JKL'
WHILE LEN(@.str) > LEN(REPLACE(@.str, ' ', ' '))
SET @.str = REPLACE(@.str, ' ', ' ')
SELECT @.str
"Terri" <terri@.cybernets.com> wrote in message
news:d3ot2k$p63$1@.reader2.nmix.net...
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1
> space.
> My reporting application can't handle this.
> Thanks
>|||"Terri" <terri@.cybernets.com> wrote in message
news:d3ot2k$p63$1@.reader2.nmix.net...
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1
> space.
> My reporting application can't handle this.
> Thanks
>
SELECT REPLACE(REPLACE(REPLACE('ABC DEF GHI',' ','[ ]'),'][ ',''),'[ ]','
')|||The short answer is to nest REPLACE() functions inside each other.
This problem came up years ago in a Newsgroup, but we then asked the
question; What is the best series of replacement sizes to use on a
string of length(n)? For example, I could use powers of 2; reduce 8
spaces to 1, then 4 spaces to 1, and finally 2 spaces to 1, which does
not work unless you run it twice). I guessed that a Fibbonacci series
might be best but could not prove it. There were some other guesses,
but one guy worked out a list by brute force.
Anyone know where to find this? Since newbies constantly make
everything in the universe VARCHAR(50), that sounds like a good upper
value to use for experiments.|||Never mind; I found it! It is over in comp.database.theory as
"Squeezing spaces out of a string" and there is some code posted for
the solutions.|||http://tinyurl.com/cx7gt
HTH,
Gert-Jan
Terri wrote:
> How can I replace multiple spaces in a varchar string?
> Given 'ABC DEF GHI' I want to return 'ABC DEF GHI'
> I want to find all occurrences of 2 or more spaces and replace with 1 spac
e.
> My reporting application can't handle this.
> Thanks|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1113589959.961601.150610@.f14g2000cwb.googlegroups.com...
> Never mind; I found it! It is over in comp.database.theory as
> "Squeezing spaces out of a string" and there is some code posted for
> the solutions.
>
Interesting thread. Here's a rewrite of my earlier response to take into
account overflow:
DECLARE @.s VARCHAR(8000)
SET @.s = 'a b c d' + SPACE(7989) + 'e'
SELECT
REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,000
1,2000),' ','[ ]'),'][ ',''),'[ ]','
') +
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,200
1,2000),' ','[ ]'),'][ ',''),'[ ]','
') +
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,400
1,2000),' ','[ ]'),'][ ',''),'[ ]','
') +
REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,600
1,2000),' ','[ ]'),'][ ',''),'[ ]','
'),
SPACE(4),' '),SPACE(3),' '),SPACE(2),' ')|||Hmm, why use 15 replaces when you only need 6?
See http://tinyurl.com/cx7gt
Gert-Jan
Chris Hohmann wrote:
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1113589959.961601.150610@.f14g2000cwb.googlegroups.com...
> Interesting thread. Here's a rewrite of my earlier response to take into
> account overflow:
> DECLARE @.s VARCHAR(8000)
> SET @.s = 'a b c d' + SPACE(7989) + 'e'
> SELECT
> REPLACE(REPLACE(REPLACE(
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,000
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> ') +
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,200
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> ') +
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,400
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> ') +
> REPLACE(REPLACE(REPLACE(SUBSTRING(@.s,600
1,2000),' ','[ ]'),'][ ',''),'[ ]'
,'
> '),
> SPACE(4),' '),SPACE(3),' '),SPACE(2),' ')|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:426033FB.8CBC2FC0@.toomuchspamalready.nl...
> Hmm, why use 15 replaces when you only need 6?
> See http://tinyurl.com/cx7gt
> Gert-Jan
>
> Chris Hohmann wrote:
The encoding from ' ' to '[ ]' has the potential to increase the size of the
string threefold. The maximum allowable length for stings is 8000. Since
8000 is not evenly divisible by 3, the string needs to be broken into at
least four parts. Once multiple spaces have been collapsed in each of the
four parts, the resulting strings are concatenated together. However, it is
possible that one part ends with a space and the following part begins with
one. The limit case for this scenario would be a string of 8000 spaces. In
which case four spaces would be concatenated together at the end. To account
for this case, I perform the three additional replace calls after the parts
have been concatenated. You could actually get away with two replace calls
at the end instead of three and eliminate the calls to SPACE. Something like
REPLACE(REPLACE
.
.
.
' ',' '),' ',' ')
In which case there would only be 14 replace calls.
HTH
-Chris Hohmann

replace funtion

how do i write a replace function that will replace a certain character with a return key (ie what happens when we do Ctrl+return key in SQL Enterprise table... so that the rest of the cell data in the column is on the next line?!

SELECT REPLACE(tasks, '/', '??') AS EXPR1
FROM log_descriptions

what should ?? be?I think you want the char function with the ascii code for line break.|||I alway forget the ANSII code for a line break so I use this instead:
SELECT REPLACE(tasks, '/', '
') AS EXPR1
FROM log_descriptions

Works like a charm :)|||or this, maybe easier to read in a big script :)

declare @.cr char(1)
set @.cr = '
'
select 'a' + @.cr + 'b'|||Char(13)+char(10)|||13 & 10... I'll try to rember that :D

So summing this up we got:
DECLARE @.cr CHAR(1)
SET @.cr = CHAR(13) + CHAR(10)
SELECT 'a' + @.cr + 'b'

Replace carriage return and line feed in a Text column?

Hi,

I've a text column (text datatype) that contains carriage return and line feed.

Syntax-wise, how can I replace these by a space?

Thanks.Hi,

I just got the answer. Thanks.

http://www.winnetmag.com/SQLServer/Article/ArticleID/20699/20699.html

SELECT
TextId,
replace ( replace(TextValue, char(10),
''), char(13), '') ModifiedTextValue
FROM
BadTextData