I get data through a xml file. I use a third party component for this.
(Zapsysys , I am in no way affiliated with them but maybe somebody knows their product)
The data in the XML looks like this:

```
<customer>
"Johnny"
</customer>

```

What I end up with in the table (customers) is a nvarchar (surname) with the following content:

```
CHAR(10)JohnnyCHAR(10)

```

This is in every nvarchar field that gets read from the XML. The component actually does extract what it reads. But those chars mess up quite a lot statements.

`select * from customers where surname = 'Johnny'` yields no results.

`select * from customers where surname like '%Johnny%'` 

or 

`select * from customers where replace(surname,char(10),'') = 'Johnny` do. 

Not very pretty.

One way to solve this is to use views with a sh\*tload of replace statements.
But wouldn't it be great if I could run a procedure that wipes these CHAR(10) from every nvarchar field?

It must be possible to write an update statement that finds all the nvarchar fields and does a replace(,"CHAR(10)",'') on them?

To be more clear: I do know how updatestatements work. I'm looking for a way to avoid writing update statements for each field in my db of type (n)varchar

update:

came up with this code after a suggestion from @matt (see answer marked as solution)

```
declare @temptable table (id 

    int identity(1,1), sql nvarchar(4000))

        insert into @temptable(sql)
        SELECT 'UPDATE '+quotename(i.TABLE_SCHEMA)+'.'+quotename(i.TABLE_NAME) +' SET 
        '+quotename(i.COLUMN_NAME)+' = REPLACE('+quotename(i.COLUMN_NAME)+', CHAR(10),'''')'
        FROM INFORMATION_SCHEMA.COLUMNS  i
        inner join sys.tables t on i.TABLE_NAME = t.name
        WHERE DATA_TYPE = 'NVARCHAR' 
        and t.type = 'U'
        and TABLE_SCHEMA = 'myschema'

        declare @i as int = 1
        declare @sql as nvarchar(max)
        declare @max as int = (select max(id) from @temptable)

        while @i <= @max
        BEGIN
        set @sql = (select [sql] from @temptable where id = @i)
        exec sp_executesql @sql
        --print cast(@i as varchar(5)) + '/'+cast(@max as varchar(5)) + ' done, ' +cast(@max-@i as varchar(5)) + ' to go...'
        set @sql = ''
        set @i = @i+1
        END

```