Tag Archives: database

SQL Case-sensitive select statement

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1
FROM Table1
WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.

SELECT Column1
FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.

EXEC sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.

Reference : Pinal Dave (http://blog.SQLAuthority.com)


SQL – Fill a column with a value

update [table] set [column] = ‘value’

 

Example:

alter table PI_15_0_0_SV add Language nchar(2) not null default ‘SV’ with values

insert into pi_15_0_0 select * from PI_15_0_0_SV


SQL – DateDiff

How I figured out that the Sharepoint 180 day trial license ran out: Sharepoint started throwing strange errors, one of them being ‘your trial license has expired’ but other things still working, so I thought maybe this is just a known bug (the bug exists and is well known!) but with no way to tell how old the license is through the licensing page in the admin site which is STUPID, I checked the install date then ran this script in a random database to save me counting wrong.

SELECT DATEADD(dd,DATEDIFF(dd,0,’2013-03-27′)+180,0) ‘Expiry date’
select datediff(day,’2013-03-27′,’2013-09-24′) as ‘Days since installation’


Count duplicate values – SQL

SELECT YourColumn, COUNT(*) TotalCount
FROM YourTable
GROUP BY YourColumn
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Copy Table with 1 line of SQL

This will copy a table structure and its data into a new table:

select * into wdsProductList_20130405 from wdsProductList

This will copy a table definition only (in MySQL) without data:

create table foo like bar;

Find a column by name (SQL Server 2008)

How to find column within a database with searching by name:

SELECT * FROM Information_Schema.Columns c WHERE  c.COLUMN_NAME like 'manu%'