We're using a PowerShell script to import AD computers into SQL; this works fine. Information about the particular computer account is entered into the description box; computer type, model number, serial number and with some, an asset tag number.
But, I'm trying to break-up the listing so that I can create a view which lists the information into separate columns.
I've gotten the description portion as well as the asset tag portion working, just not the middle three. Below, are the examples. Thank you
From:
[Description]
Joe Smith Laptop - HP EliteBook 840 G8 Notebook PC - 359Z6UT#ABA - 6EF3662A2F - AT#B-10132
John Smith Laptop - HP EliteBook 840 G8 Notebook PC - 359Z6UT#ABA - 6EF36620TM
Susan Smith WFH - HP EliteBook 840 G8 Notebook PC - 359Z6UT#ABA - 6EF36620QA
To:
[Description] [Model] [ProductNumber] [SerialNumber] [Asset Tag]
Joe Smith Laptop HP EliteBook 840 G8 Notebook PC 359Z6UT#ABA 6EF3662A2F B-10132
John Smith Laptop HP EliteBook 840 G8 Notebook PC 359Z6UT#ABA 6EF36620TM
Susan Smith WFH HP EliteBook 840 G8 Notebook PC 359Z6UT#ABA 6EF36620QA
LEFT([Description], CHARINDEX(' - HP ', [Description])) as [Description]
RIGHT([Description], LEN([Description]) - CHARINDEX(' - AT#',[Description]) -5) as [Asset Tag]
CodePudding user response：
If the delimiter is truly a - , you can use a bit of JSON to parse the string. 2016 is required for the JSON portion. If trim() is not supported use ltrim(rtrim(...))
Example or 
CodePudding user response：
If you are using an Azure SQL database or SQL Server 2022 (it's in preview now, but when someone else finds this answer in a few years, it will be relevant) you can use STRING_SPLIT() to get what you want. In the latest versions, they've added an ordinal output so you can know the position of each substring in the original string. This function will give us each section as a separate row, but we can then use PIVOT to roll each section up into their own column.
I'm assuming an id column in your input table called RowID. Also because you have a - character part of your data that isn't just a string separator, we'll also need to use STRING_AGG to put that bit back together.
Example:
;WITH cte AS (
SELECT RowID, TRIM(value) AS DataValue, ordinal FROM InputTable
CROSS APPLY STRING_SPLIT([Description],'-',1)
)
SELECT RowID,[Description],[Model],[ProductNumber],[SerialNumber],[AssetTag] FROM
(
SELECT RowID,CASE WHEN ordinal = 1 THEN 'Description' WHEN ordinal = 2 THEN 'Model' WHEN ordinal = 3 THEN 'ProductNumber' WHEN ordinal = 4 THEN 'SerialNumber' ELSE 'AssetTag' END AS ColumnName, REPLACE(STRING_AGG(DataValue,'-'),'AT#','') AS DataValue
FROM cte
GROUP BY RowID, CASE WHEN ordinal = 1 THEN 'Description' WHEN ordinal = 2 THEN 'Model' WHEN ordinal = 3 THEN 'ProductNumber' WHEN ordinal = 4 THEN 'SerialNumber' ELSE 'AssetTag' END
) x
PIVOT
(
MAX(DataValue)
FOR ColumnName IN ([Description],[Model],[ProductNumber],[SerialNumber],[AssetTag])
) AS pvt