how to change the special character when inserted in to database

Question

hi dears.

i want to change special character (i.e ! change to ?)  when inserted in to any tables of my database.

i think this can be done by triggers but i don't know syntax of such trigger. what should i do?

thanks a lot.

 

 

Answer 1

How are you inserting the data???

Are all special characters fits into your specified collation??

---

Thanks and regards, Rishabh , Microsoft Community Contributor

Answer 2

i have some applications and can not access to source of them. so i want to capture it in database.

yes. the collation is Latin .

Answer 3

Hello Dreams4gotten :)

Here is an SQL Instead Of trigger example that you can use as a base structure

Create Trigger tr_TriggerName on dbo.TableName Instead Of Insert, Update
AS
Begin

	Insert Into TableName (Col1, Col2, Col3)
	Select Col1, Col2, REPLACE(Col3,'!','?') 
	From inserted i;
 
End

 

I hope that helps,

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 4

Hello Dreams4gotten :)

Here is an SQL Instead Of trigger example that you can use as a base structure

Create Trigger tr_TriggerName on dbo.TableName Instead Of Insert, Update
AS
Begin

	Insert Into TableName (Col1, Col2, Col3)
	Select Col1, Col2, REPLACE(Col3,'!','?') 
	From inserted i;
 
End

 

I hope that helps,

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

thanks a lot but you mean i copy that trigger for all 100 tables !!!!! 

is there any way to fire this trigger for all tables of database?

 

Answer 5

If this problem is on all tables and on all fields of your database, it is not a good way to solve this by just replacing characters. It seems to be a more fundamental issue.

Generally, I use SQL Latin1 Case insensitive Accent senstive collation. And I use nvarchar() data types for string fields.

It should be possible to solve your problem using an appropriate collation

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 6

If this problem is on all tables and on all fields of your database, it is not a good way to solve this by just replacing characters. It seems to be a more fundamental issue.

Generally, I use SQL Latin1 Case insensitive Accent senstive collation. And I use nvarchar() data types for string fields.

It should be possible to solve your problem using an appropriate collation

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

 Oh !! you mean i should create custom collation? is this available in SQL server 2008?

plz instruct me about it ..

best regards.

Answer 7

No, you cannot define your own collation.

You need to understand that people here can really only answer the question you ask. We can try to guess what the underlying problem is, but the guesses will be poor at best.

You asked how to replace a certain character with a trigger, and Eralper gave you a solution. Since you did not say that it concerns 100 tables, how could he know? And does it really concern 100 tables? Not that I question that you gave 100 tables in your database, but are really all of them affected.

In any case, if you tell us the full story why you want to make these character replacements, maybe someone is able to come up with something useful.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

No, you cannot define your own collation.

You need to understand that people here can really only answer the question you ask. We can try to guess what the underlying problem is, but the guesses will be poor at best.

You asked how to replace a certain character with a trigger, and Eralper gave you a solution. Since you did not say that it concerns 100 tables, how could he know? And does it really concern 100 tables? Not that I question that you gave 100 tables in your database, but are really all of them affected.

In any case, if you tell us the full story why you want to make these character replacements, maybe someone is able to come up with something useful.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

 

hi Erland. i think you are angry about my bulky database.

i ask very simple question: " Is there any way to capture and change a special character that is imported in to the database? "

Eralper answer it so simple and it can be clever to answer carefully, because i mentioned " any tables of my database".

by the way , i like-minded with you to explain problem exactly but the answerer should be careful about question too.

thanks a lot.

Answer 9

No need for a discussion, misunderstandings, etc are very common

Sometimes the requirements are not well defined and sometimes I can also answer without understanding or guessing the right requirement

But I could not understand custom collation!

You can set your instance collation while installing SQL Server or database collation while creating your database.

Perhaps your database collation is false? Did you check it?

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

Answer 10

No need for a discussion, misunderstandings, etc are very common

Sometimes the requirements are not well defined and sometimes I can also answer without understanding or guessing the right requirement

But I could not understand custom collation!

You can set your instance collation while installing SQL Server or database collation while creating your database.

Perhaps your database collation is false? Did you check it?

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

 

thanks a lot eralper.

i think so that. yes i set collation to Latin . custom collation means that changing a specific character code with another character code !! is this possible?

 

Answer 11

i think so that. yes i set collation to Latin . custom collation means that changing a specific character code with another character code !! is this possible?

No, it is not possible. And, no, I am not angry about your database. I'm only pointing out that the answers you get depends on the question you ask.

The only thing to do what you are asking for, beside creating 100 triggers, is to install a proxy which receives the TDS packets coming on the wire, and replace characters as needed. Don't do that. Creating 100 triggers is a lot easier.

But the main question is: why do you want to replace these characters at all?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 12

i think so that. yes i set collation to Latin . custom collation means that changing a specific character code with another character code !! is this possible?

No, it is not possible. And, no, I am not angry about your database. I'm only pointing out that the answers you get depends on the question you ask.

The only thing to do what you are asking for, beside creating 100 triggers, is to install a proxy which receives the TDS packets coming on the wire, and replace characters as needed. Don't do that. Creating 100 triggers is a lot easier.

But the main question is: why do you want to replace these characters at all?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

 

the main question is not that you said. i explain my question last post with eralper.

 i want to change the specific Unicode character when filling Nvarchar field. this field might be in some tables.  what is your best solution for this problem?

i want to do that because of the language consistency reasons.

thanks.

Answer 13

The only thing to do what you are asking for, beside creating 100 triggers, is to install a proxy which receives the TDS packets coming on the wire, and replace characters as needed. Don't do that. Creating 100 triggers is a lot easier.

As specified above you need not declare several triggers.

Try calling a class/method that replaces your special characters before inserting into DB.

 

 

---

Every day its a new learning. Keep Learning!!
If this post answers your question, please click Mark As Answer . If this post is helpful please click Mark as Helpful

Answer 14

The only thing to do what you are asking for, beside creating 100 triggers, is to install a proxy which receives the TDS packets coming on the wire, and replace characters as needed. Don't do that. Creating 100 triggers is a lot easier.

As specified above you need not declare several triggers.

Try calling a class/method that replaces your special characters before inserting into DB.

 

 

---

Every day its a new learning. Keep Learning!!
If this post answers your question, please click Mark As Answer . If this post is helpful please click Mark as Helpful

 

thanks Tiya01 but i'm  misunderstanding about class/method in sql server. please explain more about it.

Answer 15

I am not talking in terms of sql server.

The way I am suggesting is on DB layer (Layer where you have define connection string and do the DB related tasks.)

What I mean is that : to create a method (either in a new or existing class of yours) before assigning DB paramater's value.

 new SqlParameter("@StringVal",ReplaceSpecialChars(stringWithSpecialChars)

where

ReplaceSpecialChars(string s)
{
            s.Replace('!', '?');
}

Hope this helps!

---

Every day its a new learning. Keep Learning!!
If this post answers your question, please click Mark As Answer . If this post is helpful please click Mark as Helpful

Answer 16

 i want to change the specific Unicode character when filling Nvarchar field. this field might be in some tables.  what is your best solution for this problem?

Sorry, since you refuse to disclose any more details, I don't have any more answers. I can't help you, if I don't know what your problem is. Write (or generate) these triggers that eralper suggested.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 17

The way I am suggesting is on DB layer (Layer where you have define connection string and do the DB related tasks.)

As I understoond dreams4gotten said he/she does not have access to the application code. Then again, we don't know why. I agree that the access layer would be the appropriate place do this - if character replacement is the solution to the root problem at all.

---

Erland Sommarskog, SQL Server MVP, [e-mail]