rename column

i need t-sql syntax for renaming a column.
the column name contains the [] characters.
alter table <tablename> rename [old field name] to NewFieldName
isn't working for me.
tia,
mcnewsxpthis works:
EXEC sp_rename 'MyTable.[Old Field]', 'NewField', 'COLUMN'|||use sp_rename..
I haven't seen the sytax that you have used.|||You might want to check this out ::
http://vadivel.blogspot.com/2004/08...g-sprename.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"mcnews" wrote:

> i need t-sql syntax for renaming a column.
> the column name contains the [] characters.
> alter table <tablename> rename [old field name] to NewFieldName
> isn't working for me.
> tia,
> mcnewsxp
>sql

removing special characters

I have a large table with a text field that includes line break characters. The line break characters are wreaking havoc on other applications, and they need to be removed and replaced with something more friendly. I found a referenced to a stored procedure that can search and replace all strings in a database, but it isn't quite what I need to do, since it only accepts valid keyboard input and not special characters.

Anyone got any suggestions?You should be able to submit the ASCII value of the special character you want to target.

blindman|||If your text field is a varchar or nvarchar you can simply replace the line breaks when selecting the data from the database.

I.E.

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

Hope this helps..|||can u post some example so that it can be tested and answered!!!!!!!!!!|||Originally posted by smorton
If your text field is a varchar or nvarchar you can simply replace the line breaks when selecting the data from the database.

I.E.

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

Hope this helps..

These are TEXT fields, unfortunately.

Next question: if I can enter the ascii value of the character in the find/replace SP, how do I do so?|||That's what this code is doing for you:

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

The CHAR() function converts and integer value to an ASCII character that the REPLACE function can accept. Some common ASCII values are:
Tab: CHAR(9)
Line feed: CHAR(10)
Carriage return: CHAR(13)

If your data is in TEXT format, you may need to use the CAST function to convert it to VARCHAR first:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

blindman|||Originally posted by blindman
That's what this code is doing for you:

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

The CHAR() function converts and integer value to an ASCII character that the REPLACE function can accept. Some common ASCII values are:
Tab: CHAR(9)
Line feed: CHAR(10)
Carriage return: CHAR(13)

If your data is in TEXT format, you may need to use the CAST function to convert it to VARCHAR first:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

blindman

I've been instructed to actually copy this table to an Access database on my desktop, where changes I make won't affect any other applications. So, I need to find an Access solution to the same problem. I'm completely clueless when it comes to Access, s I will probably be doing a LOT of research!|||Originally posted by rscrawford
I've been instructed to actually copy this table to an Access database on my desktop, where changes I make won't affect any other applications. So, I need to find an Access solution to the same problem. I'm completely clueless when it comes to Access, s I will probably be doing a LOT of research!

Kinda like going in the wrong direction...can't they just create a table for you or a small database for you on sql server?

Access was (2002 may be don't know) as feature rich as sql server...|||Originally posted by Brett Kaiser
Kinda like going in the wrong direction...can't they just create a table for you or a small database for you on sql server?


You're right, of course. I duplicated the table in question.

Now, forgive me for being a newbie, but... I have the SELECT statement from blindman:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

How do I execute this? I've opened the Query Analyzer, but I don't see where this statement allows for a FROM clause.

Bear in mind, I'm not at all used to SQL Server. Normally, I'd spend a few hours researching on the web, but this particular piece has a priority to it.|||SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

Replace @.StringToBeSearched with the column name then add your from clause.

I.E.

SELECT REPLACE(REPLACE(Cast(t.ColumnName as varchar(8000)),Char(10),' '),Char(13), '') AS 'ConvertedText'
FROM TableName t

If your column is varchar or nvarchar you do not need to cast the column. If the column is text or ntext the above cast is valid.|||SELECT REPLACE(REPLACE(Cast(t.ColumnName as varchar(8000)),Char(10),' '),Char(13), '') AS 'ConvertedText'
FROM TableName t


Thank you very much!!! That did exactly what I needed it to do!

Removing non-printing characters ...

Folks ... I have a pipe-delimited ASCII text file with a lot of
different non-printing characters. Rather than try and figure out all
the non-printing characters that exist in this 17+ million record
database, I was hoping someone might have already written a script
they'd be willing to share that would remove all non-printing
characters from an ASCII file?

Thanks,

Ralph Noble
ralph_noble@.hotmail.com"Ralph Noble" <ralph_noble@.hotmail.com> wrote in message
news:ed6de1e7.0410071845.36258199@.posting.google.c om...
> Folks ... I have a pipe-delimited ASCII text file with a lot of
> different non-printing characters. Rather than try and figure out all
> the non-printing characters that exist in this 17+ million record
> database, I was hoping someone might have already written a script
> they'd be willing to share that would remove all non-printing
> characters from an ASCII file?
> Thanks,
> Ralph Noble
> ralph_noble@.hotmail.com

This isn't really an MSSQL question, so you'll probably get a better answer
in a forum for whatever language you usually use for tasks like this - Perl,
C#, VB etc.

Simon

Removing non-printable characters

I need to remove all non-printable characters in my text and memo fields. Does SQL handle this?
Thanks in Advance!!Which SQL engine, and how do you define "printable" characters?

-PatP|||In oracle

replace(your_dirty_field,chr(10),null) will do.

You have to do one by one this way. I am not sure if translate() will do better. I think it won't even work. It never hurts to try but you can do it just as well. (That is if you have oracle)

Removing non-alpha characters & spaces script...

Hi I am trying to strip out any non-alpha characters from a field.

i.e. Field = ABC"_IT8*$ should return: ABCIT8

I am writing a loop to do this for all values of a field. The script runs, but hangs...please could somebody advise on the code below...:

I run the script but it doesn't seem to finish. Can anybody see any issues with the code:

DECLARE @.Index SMALLINT,
@.MATCH_Supplier_name varchar(500),
@.Counter numeric,
@.Max numeric
-- @.sqlstring varchar(500)

SET @.Counter = 1
SET @.Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

WHILE @.Counter <@.Max
BEGIN
SET @.MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @.Counter = DTect_Supplier_SRN)
SET @.Index = LEN(@.MATCH_Supplier_name)
WHILE @.Index > = 1
SET @.MATCH_Supplier_name = CASE
WHEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1)
WHEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1) LIKE '[0-9]' THEN SUBSTRING(@.MATCH_Supplier_name, @.Index, 1)
ELSE ''
END + @.MATCH_Supplier_name
SET @.Index = @.Index - 1
--PRINT @.MATCH_Supplier_name
SET @.Counter = @.Counter + 1
ENDSET @.Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

Change it to count ...don't have much time now,check for other errors.|||Cheers, its all sorted now.

Removing Lines from a Flat File

Hi There,

I am parsing a directory of flat files and looping through it with a foreach loop. Some of the files have lines that contain characters that I would like to remove. In fact, it would be good if I could remove the entire line. Is there a way to do this with a Script Task or some other way.

Thanks for your help.You could read the file in, use a conditional split to identify the rows you want to keep, and then output those rows to a new flat file.|||thank you! used the conditional split...

Removing Characters in Field grouping

Hello,

I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?

Code Snippet

left(Fields!TestName.Value,len(Fields!TestName.Value)-11))

you can use Rtrim|||

Are you always removing 11 character regardless of the field length or do you simply want to restrict it to a certain size?

For example, the following code restricts the length to 5 characters:

=iif(len(rtrim(Fields!TestDATA.Value)) > 5, mid(rtrim(Fields!TestDATA.Value),1,5), rtrim(Fields!TESTDATA.Value))

Removing characters

I want to remove all non-letter, non-number characters in a string. An example string would be: How#can*I^make this@.work?
I would like it to look like HowcanImakethiswork
I need to do this for a whole field. Any ideas?
ThanksCreate a user-defined function such as this:

create function CharOnly(@.TargetString varchar(500))
returns varchar(500)
as
begin
declare @.NewString varchar(50)
declare @.Counter int
set @.NewString = ''
set @.Counter = 0
while @.Counter < len(@.TargetString)
begin
set @.Counter = @.Counter + 1
if ASCII(UPPER(substring(@.TargetString, @.Counter, 1))) between 65 and 90 set @.NewString = @.NewString + substring(@.TargetString, @.Counter, 1)
end
return @.NewString
end

Then use it in your select statement like this:
select dbo.CharOnly('How#can*I^make this@.work?')

blindman|||If you are looking for a way to process regular expressions, click on the following link:

link (http://www.codeproject.com/database/xp_pcre.asp?target=xp_pcre)

removing all but alphanumeric characters from strings

I'm cleaning dirty data. One of the tasks is to remove any characters that
aren't alpha or numeric from a string. Can I use the replace function with a
character expression? I tried replace(@.dirtystring, '[^0-9a-zA-Z]', '') and
it didn't replace anything... any ideas?
here's a good link
http://groups.google.com/groups?q=steve+kass+John!Wa43yn&hl=en&lr=&ie=UTF-8&group=microsoft.public.sqlserver.programming&sel m=3CE46DFB.E728741%40drew.edu&rnum=2
your question is probably better suited for the .programming group over the
..server group.
hth,
Eric
Ann Queue wrote:
> I'm cleaning dirty data. One of the tasks is to remove any
> characters that aren't alpha or numeric from a string. Can I use the
> replace function with a character expression? I tried
> replace(@.dirtystring, '[^0-9a-zA-Z]', '') and it didn't replace
> anything... any ideas?
|||http://www.nigelrivett.net/RemoveNon...haracters.html
"Ann Queue" wrote:

> I'm cleaning dirty data. One of the tasks is to remove any characters that
> aren't alpha or numeric from a string. Can I use the replace function with a
> character expression? I tried replace(@.dirtystring, '[^0-9a-zA-Z]', '') and
> it didn't replace anything... any ideas?

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
>

Removing accented characters

I have a list of French words and want to replace all of the accented
characters with unaccented characters. I could go through and replace each
accented character one at a time, but if I can somehow do it with collations
or some other automatic method, that would be great!You can use the function REPLACE as the source for another REPLACE.
Example:
use northwind
go
create table t1 (
c1 varchar(25)
)
go
insert into t1 values('áé_óú')
go
select
*
from
t1
go
update
t1
set
c1 = replace(replace(replace(replace(replace(
c1, 'á', 'a'), 'é', 'e'), '_
',
'i'), 'ó', 'o'), 'ú', 'u')
GO
select
*
from
t1
go
drop table t1
go
AMB
"MatthewR" wrote:

> I have a list of French words and want to replace all of the accented
> characters with unaccented characters. I could go through and replace eac
h
> accented character one at a time, but if I can somehow do it with collatio
ns
> or some other automatic method, that would be great!|||In playing with this further I built on Alejandro's code to take care of mor
e
accented characters with fewer REPLACE functions:
use northwind
go
-- Create the c1 column with an accent-sensitive collation so any
comparisons done on c1 ignore accents.
create table t1 (
c1 varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AI
)
go
--use even more accented characters
insert into t1 values('áé_óúà?êí????')
go
select
*
from
t1
go
-- If you replace a letter with itself in an accent-insensitive column, the
accented letters will also be replaced.
update
t1
set
c1 = replace(replace(replace(replace(replace(
c1, 'a', 'a'), 'e', 'e'), 'i',
'i'), 'o', 'o'), 'u', 'u')
GO
select
*
from
t1
go
drop table t1
go
The value in c1 after running this is 'aeiouaaeiiuao'.
You'll end up doing more replacements this way since even non-accented
characters will be replaced, but you'll cover more accented characters with
fewer REPLACE functions.
Now if I could just figure out how to do this with just collations instead
of REPLACE functions, I'd be set!|||Matthew,
Here is a generic way to do this (not thoroughly tested, though,
particularly with different system and column collations). It will
be slow, but you are less likely to miss cases. It also replaces
with E and with e, which might be helpful.
Without using REPLACE, I can't think of any solution other
than possibly exporting to text and reimporting, either with some
bcp code page option (which I couldn't make work) or using
a third-party utility on the intermediate text file.
create table T (
s nvarchar(20)
)
insert into T values ('cre')
insert into T values ('pt de fois gras')
insert into T values ('HLNE MELANON')
go
create function unaccented(
@.s nvarchar(80)
) returns nvarchar(80) as begin
declare @.a nchar(12)
declare @.c nchar(1)
set @.a = N'AaEeIiOoUuCc'
declare @.pos int set @.pos = 0
while @.pos < len(@.s) begin
set @.pos = @.pos + 1
set @.c = substring(@.s,@.pos,1)
if @.c like '%['+@.a+']%' collate Latin1_General_CS_AI
and @.c not like '%['+@.a+']%' collate Latin1_General_CS_AS
set @.s = stuff(@.s,@.pos,1,substring(@.a,patindex(N'
%'+@.c+N'%' collate
Latin1_General_CS_AI,@.a),1))
end
return @.s
end
go
select dbo.unaccented(s)
from T
go
drop table T
drop function dbo.unaccented
-- Steve Kass
-- Drew University
"MatthewR" <MatthewR@.discussions.microsoft.com> wrote in message
news:B8577707-A67C-444A-B753-D6C8A12A663D@.microsoft.com...
> In playing with this further I built on Alejandro's code to take care of
> more
> accented characters with fewer REPLACE functions:
> --
> use northwind
> go
> -- Create the c1 column with an accent-sensitive collation so any
> comparisons done on c1 ignore accents.
> create table t1 (
> c1 varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AI
> )
> go
> --use even more accented characters
> insert into t1 values('')
> go
> select
> *
> from
> t1
> go
> -- If you replace a letter with itself in an accent-insensitive column,
> the
> accented letters will also be replaced.
> update
> t1
> set
> c1 = replace(replace(replace(replace(replace(
c1, 'a', 'a'), 'e', 'e'),
> 'i',
> 'i'), 'o', 'o'), 'u', 'u')
> GO
> select
> *
> from
> t1
> go
> drop table t1
> go
> --
> The value in c1 after running this is 'aeiouaaeiiuao'.
> You'll end up doing more replacements this way since even non-accented
> characters will be replaced, but you'll cover more accented characters
> with
> fewer REPLACE functions.
> Now if I could just figure out how to do this with just collations instead
> of REPLACE functions, I'd be set!
>