Creating query to search for a particular symbol

Hey Everyone,

I am new to SQL and need help.

I need to search an entire database called Moodle for a (ASCII) character " ? ". I wish to list every field / table that has this.

Then later on I wish to replace this symbol with an another UTF-8 symbol.

Is this possible? happy to post more information

Cheers,

Mark

Yes You can do it from SQL Server.

But I really worried about the performance, I recommand to do this task from the UI Applications.

Where you can easily built the quries & execute the dynamic quires on the db..

It might be simpler than what you try to achive at T-SQL..

|||

Hey,

Thanks for your reply.

Thats my problem I cant create queries from the application UI. We use Moodle which is a learning management system and its backend is SQL 2005. We migrated from WebCT which uses us-ASCII for encoding and Moodle uses UTF-8 which means some symbols dont work when we backup courses in Moodle which is in XML format.

I was thinking that updating the symbol via SQL would be easier. But I just wish to CREATE VIEW for symbol this database. Can this be done? and what would be the query syntax be?

Cheers,

Mark

|||

This query may help you...

Code Snippet

Createtable #Columns

(

TableNameNvarchar(1000)

,ColumnnameNVarchar(1000)

)

Execsp_msForEachtable'Insert Into #Columns

Select ''?'', name

From Syscolumns Where Xtype in (175,239,231,167)

And Id = Object_id(''?'')'

Droptable #QueryTable

Select

TableName,

'Select @.C = Count(*) From '+ TableName+' Where 1=0 '+

(Select' Or ['+ Columnname+'] Like ''%?%'''as [text()]From #Columnsas SubWhere Sub.TableName=Main.TableNameForXMLPath(''),ELEMENTS)as Query

,Row_Number()Over(OrderBy TableNameDesc) RowId1

,Row_Number()Over(OrderBy TableNameAsc) RowId2

Into #QueryTable

from

(Select TableNameFrom #ColumnsGroupBy TableName)as Main

Declare @.IInt

Select @.I= RowId1From #QueryTableWhere RowId2=1;

While @.I>0

begin

Declare @.QueryasNVarchar(max);

Declare @.TableasNVarchar(Max);

Declare @.Casint

Select

@.Query= Query,

@.Table= TableName

From

#QueryTable

Where

RowId1= @.I;

Execsp_executesql @.Query,N'@.C as Int OUTPUT',@.COUTPUT;

If @.C<> 0

Begin

Print 'Found at ' + TableName;

Select @.Query =

'Update ' + TableName + ' Set ' +

Substring((Select ',[' + Columnname + '] = Replace([' + Columnname + '],''?'',''' + Char(8) + ''') ' as [text()] From #Columns as Sub Where Sub.TableName=Main.TableName For XML Path(''), ELEMENTS),2,8000)

from

(Select TableName From #Columns Where TableName = @.Table Group By TableName) main

Exec (@.Query);

Print 'Values are replaced at ' + TableName

End

Select @.I= @.I-1;

End