Help With Substring

Transact-SQL
1

Hi, I use a database program that has a series of preset queries (with selections for the query); however, there is an optional part of the query that allows T-SQL code to add new columns, etc. in the query output.

I have a column in the database called "Identifier" It is populated with values such as this:
55666_BXY23_BYY23
51432_MBYT22_MBXX23
and so forth

The total length of the string isn't consistent and neither are the character lenghts between the "_" underscores.

What i want to make are 3 new columns delimited by the "_" underscores as such

Ident_1 Ident_2 Ident_3
55666 BXY23 BYY23
51432 MBYT22 MBXX23

I was using substring and charindex to accomplish this, but since there are two "_" underscores in this field I wasn't able to return what I wanted.

Any help would be appreciated.

Thanks

2

Standard way: use a splitter like STRING_SPLIT or dbo.DelimitedSplit8K.

Quick-and-dirty way: change the _s to .s and use PARSENAME:

SELECT identifier, 
    PARSENAME(REPLACE(identifier, '_', '.'), 1) AS ident_1, 
    PARSENAME(REPLACE(identifier, '_', '.'), 2) AS ident_2, 
    PARSENAME(REPLACE(identifier, '_', '.'), 3) AS ident_3
FROM dbo.table_name
3

Awesome, thanks. It worked.