You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
SELECT ISNULL(MyColumn1, 1.0) AS MyColumnWithoutNULL;
185
T-SQL
Strings
Prüfen ob eine Zahl in einem String enthalten ist
SELECT
value
,CASE
WHEN PATINDEX('%[0-9]%',value) > 0 THEN 1
ELSE 0
END AS CheckIfStringContainsANumber
FROM
(
SELECT 'String w/o a number' AS value
UNION ALL
SELECT 'This string contains 1 as a number' AS value
) subQuery1
-- value | CheckIfStringContainsANumber
-- ------------------------------------------------------------------
-- String w/o a number | 0
-- This string contains 1 as a number | 1
207
T-SQL
Datenbank
Manuell einen AutoIncrement/Identity/Serial Wert in Tabelle einfügen
SET IDENTITY_INSERT myTable ON
INSERT INTO myTable(Identity_ID, Value) VALUES (4711, 'Back To The Future...')
SET IDENTITY_INSERT myTable OFF
210
T-SQL
Datum, Zeit
Kalenderwoche eines Datums
select DATEPART(ISO_WEEK,CAST('2014-08-25' AS DATE))
213
T-SQL
Datenbank
DB-Funktion erstellen
-- Table-value
-- Variant 1
CREATE FUNCTION whichContinent
(@country nvarchar(15))
RETURNS TABLE
AS
RETURN
(
SELECT @country AS Country
)
-- Variant 2
CREATE FUNCTION whichContinent
(@country nvarchar(15))
RETURNS
@returnTableResult TABLE
(
id int
,currency VARCHAR(100)
)
AS
BEGIN
IF @country='Germany'
BEGIN
INSERT INTO @returnTableResult VALUES (1,'EURO')
END
RETURN
END
214
T-SQL
Exceptions
Try Catch Except
-- See also: http://msdn.microsoft.com/de-de/library/ms175976(v=sql.105).aspx
-- !!
-- TRY-CATCH cannot be used for all operations/situations in T-SQL.
-- Be careful and refer to the link above for details
-- !!
BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
217
T-SQL
Datenmengen
Anzahl Zeilen Resultset begrenzen
SELECT TOP 100 * FROM tableA
220
T-SQL
Strings
Position eines Teilstrings finden
DECLARE @searchFor_Charindex Varchar(10)
DECLARE @searchFor_Patindex Varchar(10)
DECLARE @text Varchar(100)
SET @searchFor_Charindex = 'simple'
SET @searchFor_Patindex = '%simple%' -- Remember the %. Patindex can also be used for RegEx!
SET @text = 'this is a simple text'
SELECT CHARINDEX(@searchFor_Charindex, @text) AS Position_Charindex
SELECT PATINDEX(@searchFor_Patindex, @text) AS Position_Patindex
-- 11
226
T-SQL
Systeminfo
Hostnamen ermitteln
SELECT HOST_NAME() AS Hostname;
-- prints myComputer1 or srvWindows1
241
T-SQL
Strings
Text in Großschrift oder Kleinschrift
SELECT
'my lovely Mr Singing Club' AS myString
,UPPER('my lovely Mr Singing Club') AS myStringUpper
,LOWER('my lovely Mr Singing Club') AS myStringLower
-- myString: my lovely Mr Singing Club
-- myStringUpper: MY LOVELY MR SINGING CLUB
-- myStringLower: my lovely mr singing club
