Differences

This shows you the differences between two versions of the page.

--- vendors:microsoft:sql [2024/08/23 12:21] – [What do you do when SQL Server is unusually slow?] gerardorourke
+++ vendors:microsoft:sql [2026/04/28 14:24] (current) – [Column types] gerardorourke
@@ Line 291: / Line 291: @@
 Check the Attributes "servicePrincpalName:"
+===== Parsing a string with a separator =====
+<code>
+ID	PV1	PV2
+	boo     MFA|1|boo|5:a:b:c
+	blah    MFA|2|boo|6:2
+	bop     MFA|3|boo|7:3
+	banana  blah
+	a       MFA.1.blah.2
+	b       MFA.1.blah.3
+</code>
+Using String_Split - seems the easiest way to do this - but you need a uniue ID / Primary Key - which you can group off.
+<code sql>
+WITH SplitData AS (
+    SELECT
+        ID,
+		PV2,
+        value AS PartValue,
+        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS PartNumber
+    FROM RCD
+    CROSS APPLY STRING_SPLIT(PV2, '|')
+	WHERE PV2 like 'MFA|%'
+)
+SELECT
+    ID,
+	PV2,
+    MAX(CASE WHEN PartNumber = 1 THEN PartValue END) AS Part1,
+    MAX(CASE WHEN PartNumber = 2 THEN PartValue END) AS Part2,
+    MAX(CASE WHEN PartNumber = 3 THEN PartValue END) AS Part3,
+    MAX(CASE WHEN PartNumber = 4 THEN PartValue END) AS Part4,
+    MAX(CASE WHEN PartNumber = 5 THEN PartValue END) AS Part5,
+    MAX(CASE WHEN PartNumber = 6 THEN PartValue END) AS Part6,
+    MAX(CASE WHEN PartNumber = 7 THEN PartValue END) AS Part7,
+    MAX(CASE WHEN PartNumber = 8 THEN PartValue END) AS Part8,
+    MAX(CASE WHEN PartNumber = 9 THEN PartValue END) AS Part9,
+    MAX(CASE WHEN PartNumber = 10 THEN PartValue END) AS Part10
+FROM SplitData
+GROUP BY ID, PV2
+ORDER BY ID;`
+</code>
+<code>
+ID	PV2	Part1	Part2	Part3	Part4	Part5	Part6	Part7	Part8	Part9	Part10
+	MFA|1|boo|5:a:b:c	MFA	1	boo	5:a:b:c	NULL	NULL	NULL	NULL	NULL	NULL
+	MFA|2|boo|6:2	MFA	2	boo	6:2	NULL	NULL	NULL	NULL	NULL	NULL
+	MFA|3|boo|7:3	MFA	3	boo	7:3	NULL	NULL	NULL	NULL	NULL	NULL
+</code>
+An alternative approach is to use the PARSENAME function.
+However this allows only up to 4 fields. So we would need to use different seperators - which becomes very messy.
+Why only 4?
+Because, PARSENAME (Transact-SQL) Returns the specified part of an object name.
+The parts of an object that can be retrieved are the object name, owner name, database name, and server name.
+example: SELECT PARSENAME('[Server].database.schema.table', 1) AS 'Object Name';
+<code sql>
+SELECT
+ID
+,PV2
+,LEFT(PV2, CHARINDEX(':', PV2) - 1) AS Part1
+,PARSENAME(REPLACE(PV2, '|', '.'), 4) AS MFA1
+,PARSENAME(REPLACE(PV2, '|', '.'), 3) AS MFA2
+,PARSENAME(REPLACE(PV2, '|', '.'), 2) AS MFA3
+,PARSENAME(REPLACE(LEFT(PV2, CHARINDEX(':', PV2) - 1), '|', '.'), 1) AS MFA4
+FROM RCD
+Where PV2 like 'MFA|%'
+</code>
+<code>
+ID	PV2	Part1	MFA1	MFA2	MFA3	MFA4
+	MFA|1|boo|5:a:b:c	MFA|1|boo|5	MFA	1	boo	5
+	MFA|2|boo|6:2	MFA|2|boo|6	MFA	2	boo	6
+	MFA|3|boo|7:3	MFA|3|boo|7	MFA	3	boo	7
+</code>
+==== Get SQL Column Data TYPE ====
+<code sql>
+SELECT
+    COLUMN_NAME,
+    DATA_TYPE,
+    CHARACTER_MAXIMUM_LENGTH
+FROM INFORMATION_SCHEMA.COLUMNS
+WHERE TABLE_NAME = 'YourTableName';
+</code>
+==== Check if Column is a primary Key====
+<code sql>
+SELECT
+    kcu.COLUMN_NAME
+FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
+JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
+    ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
+WHERE tc.TABLE_NAME = 'YourTableName'
+  AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
+  AND kcu.COLUMN_NAME = 'agentId';
+</code>