Split Name or whatever
One common task in SQL is given a full name, you need to split it into the first name and last name.
I recently had to split a string which contains a serial number for computer assets. The serial number might contain a pound sign (#). If it does, then we need to split it into Serial Number and Zone Name. If it doesn’t, then the entire string is just Serial Number.
Here is what I did:
DECLARE @DiscoverySerialNo VARCHAR(100)
SET @DiscoverySerialNo = ‘0509ad129c#omzsgav01’
select LEFT(@DiscoverySerialNo, ISNULL(NULLIF(CHARINDEX(‘#’, @DiscoverySerialNo) – 1, -1), LEN(@DiscoverySerialNo))) AS SerialNo
, RIGHT(@DiscoverySerialNo, NULLIF(CHARINDEX(‘#’, REVERSE(@DiscoverySerialNo)) – 1, -1)) AS ZoneName
Here is the result.
If there is no # in the string, here is the result.
SET @DiscoverySerialNo = ‘0509ad129comzsgav01’