Adam, replace the periods with a dummy value like a pipe,|, first (just some character you would never find in your data), then replace the dashes to periods, let it parse, then replace the end result's pipes back to periods. I put the new replaces in lowercase to show where to put them.
replace(PARSENAME(replace(REPLACE(GLACCT, '.' , '|' ), '-' , '.' ),6), '|' , '.' )AS PART1
Original Message:
Sent: 3/13/2025 12:17:00 PM
From: Adam Olson
Subject: RE: SQL Split GL Account field into separate columns
Gail, thanks for the help, the function does work as expected, but we have "." characters within our component values and the parsename function appears to use the . to split which is causing it to successfully execute. Any other ideas? I did add an example of a code structure
test1-test2-test3-test4-test5.100.10-test6
------------------------------
Adam Olson
Deriva Energy
------------------------------
Original Message:
Sent: 03-13-2025 11:20
From: gail smith
Subject: SQL Split GL Account field into separate columns
Try this:
SELECT GLACCT,
PARSENAME(REPLACE(GLACCT,'-','.'),6)AS PART1,
PARSENAME(REPLACE(GLACCT,'-','.'),5)AS PART2,
PARSENAME(REPLACE(GLACCT,'-','.'),4)AS PART3,
PARSENAME(REPLACE(GLACCT,'-','.'),3)AS PART4,
PARSENAME(REPLACE(GLACCT,'-','.'),2)AS PART5,
PARSENAME(REPLACE(GLACCT,'-','.'),1)AS PART6
FROM TABLE
------------------------------
gail smith
chevron
Original Message:
Sent: 03-11-2025 13:50
From: Adam Olson
Subject: SQL Split GL Account field into separate columns
Looking for some assistance in creating a SQL query to split the GL Account field Value into separate columns by GL segment. For example: If my code block consists of 6 segments split by a - as a delimiter and I would like to query and return the information into individual columns by segment.
#EverythingMaximo
#Integrations
#Reporting
------------------------------
Adam Olson
Deriva Energy
------------------------------