illegal characters in user customer data

Hello Aaron,

Here is my script:

-----------NONPRINTING CHARACTERS IDENTIFICATION-----------

/*

Created by Simon Basyuk, DBA of Carnegie Hall

--PLEASE NOTE:

the script will catch all nonprinting characters in a specified Tess table, for instance: T_CUSTOMER , T_ADDRESS

The nonprinting characters examples: CR, LF (Line Feed), Tab, Space, .. --any one from  ASCII=0 through ASCII=31  

--expected result:

cust_no=xxxxxxxx, str=Simon Basyuk

 Houshold, ASCII=10, @l_pos=12, l_ch= >

< 

*/

declare

@l_cntr                               int,

@l_pos                               int,

@l_str                                 varchar(256),

@l_ch                                  char(1),

@l_cust                              int,

@l_ASCII_start   int,@l_ASCII_end int

set @l_ASCII_start=0                                                    --HARDCODED!

set @l_ASCII_end=31--ASCII 32 is space char         --HARDCODED!

declare crs1 cursor local for SELECT

customer_no, lname  --or: fname, postal_code street1,street2 --NOTE: TABLE NAME TO BE PROVIDED!

from T_CUSTOMER --or: T_ADDRESS  for street1,street2

open crs1

fetch next from crs1 into @l_cust, @l_str

while (@@fetch_status=0)

BEGIN 

set @l_cntr=@l_ASCII_start

while @l_cntr<=@l_ASCII_end

--checking for the first occurance of the char only!

begin

  set @l_ch=char(@l_cntr)

  set @l_pos=CHARINDEX(@l_ch,@l_str)

  if @l_pos > 0

    print 'cust_no='+ convert(varchar,@l_cust)+', str='+@l_str+

    ', ASCII='+ convert(varchar,@l_cntr)+', @l_pos='+ convert(varchar,@l_pos)+', l_ch= >'+@l_ch+'<'

  if @l_cntr >=400--safety!

    BREAK

  set @l_cntr=@l_cntr+1

end --while @l_cntr<=

fetch next from crs1 into @l_cust, @l_str

END --while @@fetch_status=0

close crs1

deallocate crs1

Regards,

Simon Basyuk

Simon Basyuk Manager, Database Administration sbasyuk@carnegiehall.org

Carnegie Hall 881 Seventh Avenue New York, NY 10019

T:  212-903-9691 M:  646-296-0003

Please consider the environment before printing this e-mail.

This message may contain confidential or privileged information. If you are not the intended recipient, please advise the sender immediately by reply email and delete this message and any attachments without retaining a copy.

Hi,

I was thinking of creating a stored procedure that would detect any records in the typical tables (T_Customer, T_Address, T_Phone, T_Eaddress) with characters symbols where they dont belong, and generally inappropriate characters like tab and return etc.

Has anyone come across a report like this already? I appreciate any advice before I get started inventing the wheel here.

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Hello Aaron,

Here is my script:

-----------NONPRINTING CHARACTERS IDENTIFICATION-----------

/*

Created by Simon Basyuk, DBA of Carnegie Hall

--PLEASE NOTE:

the script will catch all nonprinting characters in a specified Tess table, for instance: T_CUSTOMER , T_ADDRESS

The nonprinting characters examples: CR, LF (Line Feed), Tab, Space, .. --any one from  ASCII=0 through ASCII=31  

--expected result:

cust_no=xxxxxxxx, str=Simon Basyuk

 Houshold, ASCII=10, @l_pos=12, l_ch= >

< 

*/

declare

@l_cntr                               int,

@l_pos                               int,

@l_str                                 varchar(256),

@l_ch                                  char(1),

@l_cust                              int,

@l_ASCII_start   int,@l_ASCII_end int

set @l_ASCII_start=0                                                    --HARDCODED!

set @l_ASCII_end=31--ASCII 32 is space char         --HARDCODED!

declare crs1 cursor local for SELECT

customer_no, lname  --or: fname, postal_code street1,street2 --NOTE: TABLE NAME TO BE PROVIDED!

from T_CUSTOMER --or: T_ADDRESS  for street1,street2

open crs1

fetch next from crs1 into @l_cust, @l_str

while (@@fetch_status=0)

BEGIN 

set @l_cntr=@l_ASCII_start

while @l_cntr<=@l_ASCII_end

--checking for the first occurance of the char only!

begin

  set @l_ch=char(@l_cntr)

  set @l_pos=CHARINDEX(@l_ch,@l_str)

  if @l_pos > 0

    print 'cust_no='+ convert(varchar,@l_cust)+', str='+@l_str+

    ', ASCII='+ convert(varchar,@l_cntr)+', @l_pos='+ convert(varchar,@l_pos)+', l_ch= >'+@l_ch+'<'

  if @l_cntr >=400--safety!

    BREAK

  set @l_cntr=@l_cntr+1

end --while @l_cntr<=

fetch next from crs1 into @l_cust, @l_str

END --while @@fetch_status=0

close crs1

deallocate crs1

Regards,

Simon Basyuk

Simon Basyuk Manager, Database Administration sbasyuk@carnegiehall.org

Carnegie Hall 881 Seventh Avenue New York, NY 10019

T:  212-903-9691 M:  646-296-0003

Please consider the environment before printing this e-mail.

This message may contain confidential or privileged information. If you are not the intended recipient, please advise the sender immediately by reply email and delete this message and any attachments without retaining a copy.

Hi,

I was thinking of creating a stored procedure that would detect any records in the typical tables (T_Customer, T_Address, T_Phone, T_Eaddress) with characters symbols where they dont belong, and generally inappropriate characters like tab and return etc.

Has anyone come across a report like this already? I appreciate any advice before I get started inventing the wheel here.

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!