• Replies 10 replies
  • Subscribers 340 subscribers
  • Views 57 views
  • Users 0 members are here
Related

illegal characters in user customer data

Aaron Glynn
$organization

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.

Parents
  • $organization

    Would the stored procedure update these incorrect findings or simply output them (say in a report?). If you're just looking to find these kinds of things, you could just set up some lists to find them. We have a variety of 'data cleanup' lists that find common errors with constituent names and addresses. The lists can be regenerated at any time and the box office staff does the cleanup. Happy to share that if you think it would be helpful.

    If you're wanting a procedure to update them automatically, I don't have something like that in place but I know others do. Hopefully one of them is watching and willing to share. :)

Reply
  • $organization

    Would the stored procedure update these incorrect findings or simply output them (say in a report?). If you're just looking to find these kinds of things, you could just set up some lists to find them. We have a variety of 'data cleanup' lists that find common errors with constituent names and addresses. The lists can be regenerated at any time and the box office staff does the cleanup. Happy to share that if you think it would be helpful.

    If you're wanting a procedure to update them automatically, I don't have something like that in place but I know others do. Hopefully one of them is watching and willing to share. :)

Children
  • $organization in reply to Beth Hawryluk

    Beth,

    We are always looking for ways to improve our clean-up efforts, and, while I shall continue to pay attention to this thread for additional ideas, I would also love to see what you have that you use in terms of 'data cleanup' lists with such common errors.

    Feel free to just either e-mail me or let me know where I should go.

    Thanks!

    John

  • $organization in reply to John A. Moskal II

    Thank you everybody for you help. I'll be giving the UP a try for now. If thats not sufficient, I'll reach out to you Beth.

  • $organization in reply to John A. Moskal II

    Dale -- should that procedure get documented in Tessitura Database Utilities? Anything else missing from there?

    On the topic of general data maintenance for customers/addresses, I have the following script set to run nightly to trim whitespace from the beginning or end of these fields:

    UPDATE c
SET c.fname = LTRIM(RTRIM(fname)),
	c.mname = LTRIM(RTRIM(mname)),
	c.lname = LTRIM(RTRIM(lname))
FROM dbo.T_CUSTOMER c
WHERE c.fname LIKE ' %' OR c.fname LIKE '% ' OR
	c.mname LIKE ' %' OR c.mname LIKE '% ' OR
	c.lname LIKE ' %' OR c.lname LIKE '% ';

UPDATE a
SET a.street1 = LTRIM(RTRIM(street1)),
	a.street2 = LTRIM(RTRIM(street2)),
	a.city = LTRIM(RTRIM(city))
FROM dbo.T_ADDRESS a
WHERE a.street1 LIKE ' %' OR a.street1 LIKE '% ' OR
	a.street2 LIKE ' %' OR a.street2 LIKE '% ' OR
	a.city LIKE ' %' OR a.city LIKE '% ';

    P.S. Neat new "Code Sample" button in the forum editor! Now if only it supported SQL highlighting...

  • $organization in reply to Nick Reilingh

    Thanks Nick, I'm checking in with our documentation team on this.

    Dale