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
myString
,locate('#',myString,5) AS findPosition -- Result is: 8
,left(myString,locate('#',myString,2)-1) AS ExtractedString -- Result is: abc
,left(myString,locate('#',myString,5)) AS ExtractedString -- Result is: abc#def#
FROM
(
SELECT
'abc#def#ghj' AS myString
) dummyString
223
SQLite
Strings
Strings miteinander verbinden
SELECT
field_a
,field_b
,field_a || field_b as together
FROM
table_1
158
T-SQL
Datenbank
Schema-Vergleich
SELECT
*
FROM
(
SELECT
TABLES_P.TABLE_SCHEMA AS [SCHEMA_P]
, TABLES_P.TABLE_NAME AS [OBJECTNAME_P]
, TABLES_P.TABLE_TYPE AS [OBJECTTYPE_P]
FROM <DATABASE_PRODUCTION>.INFORMATION_SCHEMA.TABLES AS TABLES_P
UNION ALL
SELECT
ROUTINES_P.ROUTINE_SCHEMA AS [SCHEMA_P]
, ROUTINES_P.ROUTINE_NAME AS [OBJECTNAME_P]
, ROUTINES_P.ROUTINE_TYPE AS [OBJECTTYPE_P]
FROM <DATABASE_PRODUCTION>.INFORMATION_SCHEMA.ROUTINES AS ROUTINES_P
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER TABLE' AS [OBJECTTYPE_P]
FROM
<DATABASE_PRODUCTION>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_PRODUCTION>.sys.tables AS TAB ON TRIG.parent_id = TAB.object_id
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER VIEW' AS [OBJECTTYPE_P]
FROM
<DATABASE_PRODUCTION>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_PRODUCTION>.sys.views AS TAB ON TRIG.parent_id = TAB.object_id
) ss_P
FULL OUTER JOIN
(
SELECT
TABLES_E.TABLE_SCHEMA AS [SCHEMA_E]
, TABLES_E.TABLE_NAME AS [OBJECTNAME_E]
, TABLES_E.TABLE_TYPE AS [OBJECTTYPE_E]
FROM <DATABASE_DEVELOPMENT>.INFORMATION_SCHEMA.TABLES AS TABLES_E
UNION ALL
SELECT
ROUTINES_E.ROUTINE_SCHEMA AS [SCHEMA_E]
, ROUTINES_E.ROUTINE_NAME AS [OBJECTNAME_E]
, ROUTINES_E.ROUTINE_TYPE AS [OBJECTTYPE_E]
FROM <DATABASE_DEVELOPMENT>.INFORMATION_SCHEMA.ROUTINES AS ROUTINES_E
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER TABLE' AS [OBJECTTYPE_P]
FROM
<DATABASE_DEVELOPMENT>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_DEVELOPMENT>.sys.tables AS TAB ON TRIG.parent_id = TAB.object_id
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER VIEW' AS [OBJECTTYPE_P]
FROM
<DATABASE_DEVELOPMENT>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_DEVELOPMENT>.sys.views AS TAB ON TRIG.parent_id = TAB.object_id
) ss_E ON ss_P.[SCHEMA_P]=ss_E.[SCHEMA_E] AND ss_P.OBJECTNAME_P=ss_E.OBJECTNAME_E AND ss_P.OBJECTTYPE_P=ss_E.OBJECTTYPE_E
293
BigQuery
Datum, Zeit
Gestriges Datum
SELECT
CURRENT_TIMESTAMP() AS today_timestamp
, DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY) AS yesterday_as_date
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
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
253
T-SQL
Metadaten
Fremdschlüssel auflisten
SELECT
[FK_NAME] = obj.name
,[Schema_Name] = sch.name
,[Table] = tab1.name
,[Column] = col1.name
,[Referenced_Schema_Name] = sch2.name
,[Referenced_Table] = tab2.name
,[Referenced_Column] = col2.name
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
INNER JOIN sys.schemas sch2
ON tab2.schema_id = sch.schema_id
245
PL/pgSQL
Datenbank
SQL SELECT Statische Werte
SELECT
*
FROM (
VALUES
(1, 2),
(3, 4)
) AS q (col1, col2)
;
-- the same as:
SELECT 1 AS col1, 2 AS col2 UNION ALL
SELECT 3 AS col1, 4 AS col2
;
-- col1 | col2
-- ===========
-- 1 | 2
-- -----------
-- 3 | 4
244
T-SQL
Datenbank
SQL SELECT Statische Werte
SELECT
*
FROM (
VALUES
(1, 2),
(3, 4)
) AS q (col1, col2)
GO
-- the same as:
SELECT 1 AS col1, 2 AS col2 UNION ALL
SELECT 3 AS col1, 4 AS col2
GO
-- col1 | col2
-- ===========
-- 1 | 2
-- -----------
-- 3 | 4
