Feeds:
Posts
Comments

Archive for the ‘T-SQL’ Category

Collation precedence conflict in SQL Server 2000

The collation precedence rules apply only to the character string data types, char, varchar, text, nchar, nvarchar, and ntext. Objects with other data types do not participate in collation evaluations.
link to article on msdn
A simple example is as follows

create table #TempRO
(
Id  int Identity (1,1),
App_No     NVarchar (100)
)
— insert some values in this table
Select *
from #TempRO T
Inner join App AO
On T.App_No = AO.App_No

We are having the App table using a different collation then the default that is “Arabic_CI_AS”

So the above select will give the following collation error, to remove it we just add the following statement while defining the temp table

create table #TempRO
(
Id  int Identity (1,1),
App_No     NVarchar (100)    collate Arabic_CI_AS
)

This not only happens in temp tables but also in the normal tables or at times if we are comparing columns so its better to use sp_help TableName or the syscolumns to find the collation of the two.

Happy Collating 😉

Advertisements

Read Full Post »

A lot of you might have come across this problem just like me.

 

Firstly we create a temporary table.

create table #temp1

(

            id Int Identity(1,1),

            invoice varchar (20)

)

insert into #temp1 values (‘CR’)

insert into #temp1 values (‘CR ‘)

insert into #temp1 values (‘ CR ‘)

insert into #temp1 values (‘ CR’)

 

Select *  From #temp1

            where DATALENGTH(invoice) <> len(invoice)

 

The len function in SQL gives you the length of the characters including the spaces white DataLength excludes the trailing not preceding spaces.

 

Here if you don’t have the column defined as varchar rather you have an nvarchar field then you simply convert then to varchar before calling the respective functions like this.

 

Select *  From #temp1

            where DATALENGTH(convert(varchar(20),invoice)) <> len(convert(varchar(20),invoice))

 

 

Happy searching trailing spaces in your db, please share with us if you have better options.

Read Full Post »

If you would like to find some charater/s in a column in the table and replace it here is a simple querry.

Note: For beginner please make sure you know what an update statement does. This query is going to update the column values in your table. So if you are just testing please use the Select statement instead. I have added it to the bottome of this article.

Approach 1:
Update T
       Set T.Col1 = Replace (T.Col1, “@” , ‘at’)
       From MyTable T

But If we just want to make it a bit faster depending on the size of your table.
Approach 2:
Update T
       Set T.Col1 = Replace (T.Col1,”@”,’at’)
       From MyTable T
       Where T.Col1 like (“@”)

You can have a hang of what the Replace functions does from the following link. But I would give you a little idea over here too. The Replace function look for the string that you have provided as the second parameter in the table column or string expression  that you have provided as the first parameter and simply replaces that string with the third parameter.

*Select Replace(T.Col1,”@”, ‘at’) As [ColName]
      From MyTable T

Digg!
 

Read Full Post »