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

Other SQL Server Topics
1

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

2

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.

3

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.

4

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.

5

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!

6

Using SUBSTRING only:

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

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

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
8

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