Easy way to remove international alphabet from all rows?

Is there an easy way to loop through all rows and remove all
international alphabet characters from a column in a table, for example
remove German umlauts "" and convert them to a simple "u".
Thanks,
lqSee the following thread:
http://groups-beta.google.com/group...b45abaa5cf410b8

Razvan|||OK< thanks on that. How about keeping out these characters in the first
place?|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Is there an easy way to loop through all rows and remove all
> international alphabet characters from a column in a table, for example
> remove German umlauts "" and convert them to a simple "u".

Here is a much smarter version than the one in the linkn Razvan foun

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')

update T
set s = convert(varchar(20), s) COLLATE Cyrillic_General_CS_AS
go
select * from T
go
drop table T

This assumes that your data only has Latin characters.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> OK< thanks on that. How about keeping out these characters in the first
> place?

I don't know, nuke the parts of the world where we insist on using other
languages than English?

Seriously, I think it would be a very bad idea. If you want to send
mail to these persons, you do want to spell their names properly, don't
you?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's only one of the problems! I want to store the data with the
international characters but then I need to strip out the international
characters for certain output, such as in e-mail addresses...
lq|||Technically, international characters are permitted in email addresses
and domain names so stripping them out is still a bad idea.

You'll have to build your own table to translate characters that you
deem to be unacceptable. Google for some conversion tables but there
isn't really a single answer because it depends on which characters you
need to support (don't forget the two-byte character sets used in the
Far East and elsewhere for example).

--
David Portas
SQL Server MVP
--|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> That's only one of the problems! I want to store the data with the
> international characters but then I need to strip out the international
> characters for certain output, such as in e-mail addresses...

I would recomment that you store the e-mail address. While the e-mail
address often can be formed as firstname.lastname@.domain.xxx, this is
not a requirement.

And the local transformation may not be the one you expect. in German
after often replaced with ae, oe and ue. (Whether this is actually common
in mail addresses, I don't know.)

If you are thinking of the comment part of the e-mail address:

John Smith <johnsmith@.example.com
(John Smith is the comment here) You should either encode the comment
according to MIME as per RFC2047, or send as-is. But don't misspell
people's names.

(By the way, permit me to point out that the term "international characters"
is a misnomer. The internationalest characters I can think of is the
English alphabet A-Z. Non-ASCII characters is a better term, and
technically precise.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks for that. You're right, I do mean non-ASCII characters...
This gets tricky because many city names and people names contain
non-ASCII characters and I want to display then properly, however, some
systems do not support non-ASCII characters and they need to be
cleansed for output to those systems, so as you point out, there is not
an always correct translation into ASCII characters.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Thanks for that. You're right, I do mean non-ASCII characters...
> This gets tricky because many city names and people names contain
> non-ASCII characters and I want to display then properly, however, some
> systems do not support non-ASCII characters and they need to be
> cleansed for output to those systems, so as you point out, there is not
> an always correct translation into ASCII characters.

Systems in 2005 that is not capable to handle non-ASCII characters? So,
not all programs are capable to handle Unicode, but one would at least
imagine that they should be able handle 8-bit charset. If I were you,
I would consider to attempt to kill these systems by starvation and
not feed them any data. :-)

On a more serious point, there are one letter that the snippet I
posted does not handle and that is . This gets converted into a ?.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Windows file names is an example on one such system...|||Which Windows are you referring to? FAT32? NTFS does support non-ASCII
characters in files names, at least it does under Win 2003 so I don't
know why it wouldn't under other versions.

--
David Portas
SQL Server MVP
--|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Windows file names is an example on one such system...

The 8.3 names, yes, but you rarely have to see them. Even less rarely
have to form them. As long as you are on NTFS you can use the whole range
of Unicode, and use letters from any script. The FAT files systems may
not support this, but I'm pretty sure that they support the full range
of the current ANSI code page.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

easily editting fields with more than 1024 characters

i suspect i know the answer to this already, but here goes anyway...

i have a table that has field of varchar(2048), which once in a blue moon i
need to edit the data manually (until a bad character parser validates the
data before it's written ;-) )
at the moment i'm doing this through enterprise manager (sql server 2000),
opening the table then filtering using where clauses etc to see the records
i'm interested in. i then edit the data direct in the results pane (purely
because it's quicker than entering the UPDATE transact SQL). this is fine
until i hit a record that has 1024 or more characters in the field. all i
can do is delete all the data. if i try and paste the same data into the
field again, it'll truncate the record to the first 1024 chars (unconfirmed)
despite the field being able to take double that.

i've googled this and the result basically said "don't be lazy, do it
through UPDATE transact SQL in the query analyser".

anyone know if that's my only option or is there a patch / whatever to allow
me to keep using entman as i lazily do at the mo?

cheers!dave (usenet@.polo.devilgas.com) writes:
> i suspect i know the answer to this already, but here goes anyway...
> i have a table that has field of varchar(2048), which once in a blue
> moon i need to edit the data manually (until a bad character parser
> validates the data before it's written ;-) ) at the moment i'm doing
> this through enterprise manager (sql server 2000), opening the table
> then filtering using where clauses etc to see the records i'm interested
> in. i then edit the data direct in the results pane (purely because it's
> quicker than entering the UPDATE transact SQL). this is fine until i hit
> a record that has 1024 or more characters in the field. all i can do is
> delete all the data. if i try and paste the same data into the field
> again, it'll truncate the record to the first 1024 chars (unconfirmed)
> despite the field being able to take double that.
> i've googled this and the result basically said "don't be lazy, do it
> through UPDATE transact SQL in the query analyser".
> anyone know if that's my only option or is there a patch / whatever to
> allow me to keep using entman as i lazily do at the mo?

As I just said in another post, the Open Table function in Enterprise
Manager is a convenience function and not a replacement for an application
or even in class with Access or Excel. There are several shortcomings
with Open Table. In this particular case, I believe there is a limit of
around 1000 characters io EM.

So you just start typing UPDATE commands. By the time, you've gotten some
exercise, you will find that that is faster in the long run, because
scripts are repeatable, while point-and-click GUIs are not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9698EF7C2DE54Yazorman@.127.0.0.1...
> As I just said in another post, the Open Table function in Enterprise
> Manager is a convenience function and not a replacement for an application
> or even in class with Access or Excel. There are several shortcomings
> with Open Table. In this particular case, I believe there is a limit of
> around 1000 characters io EM.
> So you just start typing UPDATE commands. By the time, you've gotten some
> exercise, you will find that that is faster in the long run, because
> scripts are repeatable, while point-and-click GUIs are not.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

thanks erland. i can assure you that in this case, it's a lot faster doing
it from EM than transact SQL. by the time i've copied and pasted the 1000+
characters, id field etc etc then edit the chars i want to change in the SQL
it's considerably longer.
thankfully these changes are once in a blue moon and only affect a single
record.

Dynamic SQL Over 4000 characters

I have created a parameterized statement in a T-SQL procedure that uses
sp_executesql such as the following:
--****************ORIGINAL STATEMENT*************************
DECLARE @.sql nvarchar(4000)
SET @.sql = ' SELECT
...
FROM
...
WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC sp_executesql @.sql,
N'@.User bit,
@.FByCar bit,
@.FByFac varchar(50)',
@.User,
@.FByCar,
@.FByFac
My problem is that often my sql string (my real one, not this modified
replica I have included here) is going to be over 4000 characters. I also am
concerned about SQL Injection, which is the reason for desiring a
parameterized statement such as the one above.
In trying to rewrite it so that the sql string can be over 4000 characters
and wrapped into EXEC(), I have written the following:
--****************REVISED STATEMENT*************************
DECLARE @.sqlFrom nvarchar(4000)
DECLARE @.sqlWhere nvarchar(4000)
SET @.sqlFrom = ' SELECT
...
FROM
... '
SET @.sqlWhere = ' WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + @.User + ''',
@.FByCar bit,
@.FByCar = ''' + @.FByCar + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + @.FByFac + '''')
My questions:
1. Am I still as protected from SQL Injection writing the Revised statement
as in the Original?
2. In the Revised statement, specifically the INSERT, can the variables be
assigned in a cleaner fashion, such as in the Original statement?
--
Message posted via http://www.sqlmonster.comHi
I think you should be looking at what you are trying to achieve and possibly
split it up, rather than trying to get a statement of over 4000 characters to
execute. There may be scope to use views and or functions to reduce the size
a little. As you concatenating your input variables you may still be subject
to SQL Injection, use of the QUOTENAME function may help.
John
"cbrichards" wrote:
> I have created a parameterized statement in a T-SQL procedure that uses
> sp_executesql such as the following:
> --****************ORIGINAL STATEMENT*************************
> DECLARE @.sql nvarchar(4000)
> SET @.sql => ' SELECT
> ...
> FROM
> ...
> WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC sp_executesql @.sql,
> N'@.User bit,
> @.FByCar bit,
> @.FByFac varchar(50)',
> @.User,
> @.FByCar,
> @.FByFac
> My problem is that often my sql string (my real one, not this modified
> replica I have included here) is going to be over 4000 characters. I also am
> concerned about SQL Injection, which is the reason for desiring a
> parameterized statement such as the one above.
> In trying to rewrite it so that the sql string can be over 4000 characters
> and wrapped into EXEC(), I have written the following:
> --****************REVISED STATEMENT*************************
> DECLARE @.sqlFrom nvarchar(4000)
> DECLARE @.sqlWhere nvarchar(4000)
> SET @.sqlFrom => ' SELECT
> ...
> FROM
> ... '
> SET @.sqlWhere => ' WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + @.User + ''',
> @.FByCar bit,
> @.FByCar = ''' + @.FByCar + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + @.FByFac + '''')
> My questions:
> 1. Am I still as protected from SQL Injection writing the Revised statement
> as in the Original?
> 2. In the Revised statement, specifically the INSERT, can the variables be
> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks John. Given the Revised statement, would I use the QuoteName function
within the Set Statement:
SET @.sqlWhere = ' WHERE
field1 = quotename(@.User, '''')
AND
field2 = quotename(@.FByCar, '''')
AND
field3 LIKE quotename(@.FByFac, '''')'
Or would I use QuoteName within the Insert statement:
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + quotename(@.User, '''') + ''',
@.FByCar bit,
@.FByCar = ''' + quotename(@.FByCar, '''') + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + quotename(@.FByFac, '''') + '''')
Or would I use it in both?
John Bell wrote:
>Hi
>I think you should be looking at what you are trying to achieve and possibly
>split it up, rather than trying to get a statement of over 4000 characters to
>execute. There may be scope to use views and or functions to reduce the size
>a little. As you concatenating your input variables you may still be subject
>to SQL Injection, use of the QUOTENAME function may help.
>John
>> I have created a parameterized statement in a T-SQL procedure that uses
>> sp_executesql such as the following:
>[quoted text clipped - 63 lines]
>> 2. In the Revised statement, specifically the INSERT, can the variables be
>> assigned in a cleaner fashion, such as in the Original statement?
--
Message posted via http://www.sqlmonster.com|||Hi
It would be the second option.
John
"cbrichards via SQLMonster.com" wrote:
> Thanks John. Given the Revised statement, would I use the QuoteName function
> within the Set Statement:
> SET @.sqlWhere => ' WHERE
> field1 = quotename(@.User, '''')
> AND
> field2 = quotename(@.FByCar, '''')
> AND
> field3 LIKE quotename(@.FByFac, '''')'
>
> Or would I use QuoteName within the Insert statement:
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + quotename(@.User, '''') + ''',
> @.FByCar bit,
> @.FByCar = ''' + quotename(@.FByCar, '''') + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + quotename(@.FByFac, '''') + '''')
>
> Or would I use it in both?
>
> John Bell wrote:
> >Hi
> >
> >I think you should be looking at what you are trying to achieve and possibly
> >split it up, rather than trying to get a statement of over 4000 characters to
> >execute. There may be scope to use views and or functions to reduce the size
> >a little. As you concatenating your input variables you may still be subject
> >to SQL Injection, use of the QUOTENAME function may help.
> >
> >John
> >
> >> I have created a parameterized statement in a T-SQL procedure that uses
> >> sp_executesql such as the following:
> >[quoted text clipped - 63 lines]
> >> 2. In the Revised statement, specifically the INSERT, can the variables be
> >> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.sqlmonster.com
>

Dynamic SQL Over 4000 characters

I have created a parameterized statement in a T-SQL procedure that uses
sp_executesql such as the following:
--****************ORIGINAL STATEMENT*************************
DECLARE @.sql nvarchar(4000)
SET @.sql =
' SELECT
..
FROM
..
WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC sp_executesql @.sql,
N'@.User bit,
@.FByCar bit,
@.FByFac varchar(50)',
@.User,
@.FByCar,
@.FByFac
My problem is that often my sql string (my real one, not this modified
replica I have included here) is going to be over 4000 characters. I also am
concerned about SQL Injection, which is the reason for desiring a
parameterized statement such as the one above.
In trying to rewrite it so that the sql string can be over 4000 characters
and wrapped into EXEC(), I have written the following:
--****************REVISED STATEMENT*************************
DECLARE @.sqlFrom nvarchar(4000)
DECLARE @.sqlWhere nvarchar(4000)
SET @.sqlFrom =
' SELECT
..
FROM
.. '
SET @.sqlWhere =
' WHERE
field1 = @.User
AND
field2 = @.FByCar
AND
field3 LIKE @.FByFac'
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + @.User + ''',
@.FByCar bit,
@.FByCar = ''' + @.FByCar + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + @.FByFac + '''')
My questions:
1. Am I still as protected from SQL Injection writing the Revised statement
as in the Original?
2. In the Revised statement, specifically the INSERT, can the variables be
assigned in a cleaner fashion, such as in the Original statement?
Message posted via http://www.droptable.comHi
I think you should be looking at what you are trying to achieve and possibly
split it up, rather than trying to get a statement of over 4000 characters t
o
execute. There may be scope to use views and or functions to reduce the size
a little. As you concatenating your input variables you may still be subject
to SQL Injection, use of the QUOTENAME function may help.
John
"cbrichards" wrote:

> I have created a parameterized statement in a T-SQL procedure that uses
> sp_executesql such as the following:
> --****************ORIGINAL STATEMENT*************************
> DECLARE @.sql nvarchar(4000)
> SET @.sql =
> ' SELECT
> ...
> FROM
> ...
> WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC sp_executesql @.sql,
> N'@.User bit,
> @.FByCar bit,
> @.FByFac varchar(50)',
> @.User,
> @.FByCar,
> @.FByFac
> My problem is that often my sql string (my real one, not this modified
> replica I have included here) is going to be over 4000 characters. I also
am
> concerned about SQL Injection, which is the reason for desiring a
> parameterized statement such as the one above.
> In trying to rewrite it so that the sql string can be over 4000 characters
> and wrapped into EXEC(), I have written the following:
> --****************REVISED STATEMENT*************************
> DECLARE @.sqlFrom nvarchar(4000)
> DECLARE @.sqlWhere nvarchar(4000)
> SET @.sqlFrom =
> ' SELECT
> ...
> FROM
> ... '
> SET @.sqlWhere =
> ' WHERE
> field1 = @.User
> AND
> field2 = @.FByCar
> AND
> field3 LIKE @.FByFac'
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + @.User + ''',
> @.FByCar bit,
> @.FByCar = ''' + @.FByCar + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + @.FByFac + '''')
> My questions:
> 1. Am I still as protected from SQL Injection writing the Revised statemen
t
> as in the Original?
> 2. In the Revised statement, specifically the INSERT, can the variables be
> assigned in a cleaner fashion, such as in the Original statement?
> --
> Message posted via http://www.droptable.com
>|||Thanks John. Given the Revised statement, would I use the QuoteName function
within the Set Statement:
SET @.sqlWhere =
' WHERE
field1 = quotename(@.User, '''')
AND
field2 = quotename(@.FByCar, '''')
AND
field3 LIKE quotename(@.FByFac, '''')'
Or would I use QuoteName within the Insert statement:
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + quotename(@.User, '''') + ''',
@.FByCar bit,
@.FByCar = ''' + quotename(@.FByCar, '''') + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + quotename(@.FByFac, '''') + '''')
Or would I use it in both?
John Bell wrote:[vbcol=seagreen]
>Hi
>I think you should be looking at what you are trying to achieve and possibl
y
>split it up, rather than trying to get a statement of over 4000 characters
to
>execute. There may be scope to use views and or functions to reduce the siz
e
>a little. As you concatenating your input variables you may still be subjec
t
>to SQL Injection, use of the QUOTENAME function may help.
>John
>
>[quoted text clipped - 63 lines]
Message posted via http://www.droptable.com|||Hi
It would be the second option.
John
"cbrichards via droptable.com" wrote:

> Thanks John. Given the Revised statement, would I use the QuoteName functi
on
> within the Set Statement:
> SET @.sqlWhere =
> ' WHERE
> field1 = quotename(@.User, '''')
> AND
> field2 = quotename(@.FByCar, '''')
> AND
> field3 LIKE quotename(@.FByFac, '''')'
>
> Or would I use QuoteName within the Insert statement:
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + quotename(@.User, '''') + ''',
> @.FByCar bit,
> @.FByCar = ''' + quotename(@.FByCar, '''') + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + quotename(@.FByFac, '''') + '''')
>
> Or would I use it in both?
>
> John Bell wrote:
> --
> Message posted via http://www.droptable.com
>|||Thanks John. Given the Revised statement, would I use the QuoteName function
within the Set Statement:
SET @.sqlWhere =
' WHERE
field1 = quotename(@.User, '''')
AND
field2 = quotename(@.FByCar, '''')
AND
field3 LIKE quotename(@.FByFac, '''')'
Or would I use QuoteName within the Insert statement:
INSERT #T1
EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
N''@.User bit,
@.User = ''' + quotename(@.User, '''') + ''',
@.FByCar bit,
@.FByCar = ''' + quotename(@.FByCar, '''') + ''',
@.FByFac varchar(50)',
@.FByFac = ''' + quotename(@.FByFac, '''') + '''')
Or would I use it in both?
John Bell wrote:[vbcol=seagreen]
>Hi
>I think you should be looking at what you are trying to achieve and possibl
y
>split it up, rather than trying to get a statement of over 4000 characters
to
>execute. There may be scope to use views and or functions to reduce the siz
e
>a little. As you concatenating your input variables you may still be subjec
t
>to SQL Injection, use of the QUOTENAME function may help.
>John
>
>[quoted text clipped - 63 lines]
Message posted via http://www.droptable.com|||Hi
It would be the second option.
John
"cbrichards via droptable.com" wrote:

> Thanks John. Given the Revised statement, would I use the QuoteName functi
on
> within the Set Statement:
> SET @.sqlWhere =
> ' WHERE
> field1 = quotename(@.User, '''')
> AND
> field2 = quotename(@.FByCar, '''')
> AND
> field3 LIKE quotename(@.FByFac, '''')'
>
> Or would I use QuoteName within the Insert statement:
> INSERT #T1
> EXEC('EXEC sp_executesql N''' + @.sqlFrom + @.sqlWhere + ''',
> N''@.User bit,
> @.User = ''' + quotename(@.User, '''') + ''',
> @.FByCar bit,
> @.FByCar = ''' + quotename(@.FByCar, '''') + ''',
> @.FByFac varchar(50)',
> @.FByFac = ''' + quotename(@.FByFac, '''') + '''')
>
> Or would I use it in both?
>
> John Bell wrote:
> --
> Message posted via http://www.droptable.com
>