Home > SQL Challenge > Split Name or whatever

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.

image

If there is no # in the string, here is the result.

SET @DiscoverySerialNo = ‘0509ad129comzsgav01’

image

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: