SQLTeam.com | Weblogs | Forums

Automatically split data from one column and insert it into two separate columns?

I'm building a relatively simple database. The goal is to compare the recorded weight of a bag, as presented in bar code form on the bag itself, to the actual weight of the bag as weighed by a user by putting the bag on a scale. Easy peasy. One hurdle I need to overcome is that the data from the bags' barcodes contains both the weight and lot number. I need a way to automatically split out those two pieces of data and insert it into separate columns in the database when the data is entered. I'm assuming there's a way to make this happen. The scanned data is uniform, so splitting it at a specific number of characters won't be a problem. I seem to remember doing something similar in the past, but as I don't do this sort of thing on the regular, it has vanished from my brain. I vaguely recall something about a trigger. Any help would be greatly appreciated.

Thanks,
Sam

Some sample combined string data would make it a lot easier to provide help. Keep in mind, we have NO idea what your data looks like.

There are a bunch of different splitters out there, but Jeff's work the best. The link is below. Also, if they are uniform, all the time, you can always use Left and Right.

These splitters will split delimited strings into separate rows - which is not what the OP is requesting. They can be made to work but probably not the best approach.

It will depend on how the string is constructed - and my guess is the string has a fixed data by position. If so - then a simple SUBSTRING can be used...if not then a combination of SUBSTRING and CHARINDEX will suffice to parse the data into separate columns.

The data is in the form of:

3.9315B0020025

Where the 3.9315 is the weight, and the B0020025 is the lot number. It will always be a six character weight followed by an eight character lot number.

I'll take a gander at substrings and charindex.

Thanks!

Using SUBSTRING only:

Declare @testString char(14) = '3.9315B0020025';

 Select weight = substring(@testString, 1, 6)
      , lot_number = substring(@testString, 7, 8);
1 Like

If the lot number always starts with a letter, you could easily handle different length weights:

SELECT 
    string,
    LEFT(string, PATINDEX('%[A-Z]%', string + 'X') - 1) AS weight,
    SUBSTRING(string, PATINDEX('%[A-Z]%', string + 'X') + 1, 8) AS lot_number
FROM ( VALUES
    ('3.9315B0020025'), ('4.56785D0040045'), ('8.124H001045'), ('9.9915') ) AS strings(string)
2 Likes

Thanks, everyone that contributed. Between the offerings here and some previous code in a trigger on another database, I was able to piece together what I needed. Now... to get the data into the database. :slight_smile:

Thanks again,
Sam