is there a function in sql server that will check a string for illegal
characters?  i have a column that sometimes contains values like:
? ???¦++++S+n¦÷+¦ú¼-·+½+-+-¦p++¦÷¦÷¦-ú+ú¼-¦°-«¦+¦«¦-+¡--+++Ñúí+¦

i want to clean this table by deleting records that have these types of
characters- does this have something to do with unicode format?

tia

Simply use the replace function as follow:
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
        YourColumnName,
          '-',''),' ',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),
          '^',''),'&',''),'*',''),'(',''),')',''),'_',''),'+',''),
          '`',''),'=',''),',',''),'.',''),'/',''),'?',''),'"',''),
          ';',''),':',''),'[',''),']',''),'{',''),'}',''),char(39),''))

sa



JTL wrote:
Show quoteHide quote

my god!!!

??????:1152939355.056237.319***@b28g2000cwb.googlegroups.com...
Simply use the replace function as follow:
replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
YourColumnName,
      '-',''),' ',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),
      '^',''),'&',''),'*',''),'(',''),')',''),'_',''),'+',''),
      '`',''),'=',''),',',''),'.',''),'/',''),'?',''),'"',''),
      ';',''),':',''),'[',''),']',''),'{',''),'}',''),char(39),''))

sa



JTL wrote:
Show quoteHide quote

As long as you are certain that these characters are illegal as a
singularity (in other words a single question mark hould trigger your
data cleansing process), you can use like and set delimiter, eg:

SELECT *
FROM Table
WHERE Column LIKE '%[?¦+¼]%'

If this is not the case, google SQL Server and RegEx.

HTH,
Stu



JTL wrote:
Show quoteHide quote

master table and child table andfield concatenation and loop records ?
VIEW's ORDER BY columns necessary in column list
Question for T-SQL Gurus
FOREIGN KEY that REFERENCES a server login for a database?
How to get the record created date?