Tag Archives: sql

SQL – Select into XML structure

select 
d.sup_productCode
,m.tpi_familyCode
,m.tpi_propertyCode
,d.sup_propertyValue
,m.map_type
,d.sup_familyCode
,d.sup_propertyCode
,m.note
from imp_dataSource d
join def_mapping m on m.sup_propertyCode = d.sup_propertyCode
where m.sup_familyCode = d.sup_familyCode or m.sup_familyCode ='GENERIC'
and m.map_type in ('SIMPLE', 'COMPLEX')
and d.sup_propertyValue != '' and d.sup_propertyValue is not null
order by d.sup_familyCode, d.sup_productCode, d.sup_propertyCode
for xml auto, type, elements, root('Products')

MS SQL – Removing non-printable characters

http://iso30-sql.blogspot.de/2010/10/remove-non-printable-unicode-characters.html


SQL select on XML type column

SELECT column
FROM table
where column.value(‘(/xpath/expression/parameter[@name=”supplierID”])[1]’, ‘nvarchar(100)’) = ‘matchValue’


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)


Update Table A column from Table B

update TableA set col = TableB.col
from TableA, TableB
where TableA.matchCol = TableB.matchCol


SQL Order tables by creation date

SELECT
[name]
,create_date
,modify_date
FROM
sys.tables
order by 2 desc


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