0

I need a query to seperate "GL_CODE" into different columns based the "." as the delimiter.

I have attached a screenshot of the setup i have done, but im getting error after extracting the 7th segment.

Note that the elements in each are not having same string length for every line.

Code that I have used:

select 
    YEAR, PERIOD, AMOUNT, 
    SUBSTRING(GL_CODE, 1, 3) AS segment1,
    SUBSTRING(GL_CODE, CHARINDEX('.', GL_CODE, 1) + 1, CHARINDEX('.', GL_CODE, (CHARINDEX('.', GL_CODE, 1)))) AS segment2  
FROM 
    TAG_ENTITY_GL_YTD_BAL

enter image description here

Dreamcatcher_AR
  • 61
  • 6
  • Images of data, and code, don't help us help you, so its difficult for us to see what's wrong with that. I've marked this as a duplicate anyway, which shows you how to split delimited values into columns. I suggest the answer that uses XML. – Thom A Feb 04 '20 at 08:14
  • my requirement is to do this in SQL Server Management Studio 2018. I don think the other answers are of help to me. – Dreamcatcher_AR Feb 04 '20 at 09:32
  • *"I don think the other answers are of help to me."* Why not? What doesn't help about them? SSMS is just an IDE, it's not an RDBMS. – Thom A Feb 04 '20 at 09:33
  • Could you please help me by modifying the script as per my need. This is how i need it : SELECT YEAR, PERIOD, AMOUNT, SUBSTRING(GL_CODE, 1, 3) AS segment1 FROM TAG_ENTITY_GL_YTD_BAL Similarly each GL_CODE should be sepearted as segment 2, Segment 3, ..Etc – Dreamcatcher_AR Feb 04 '20 at 09:40
  • i'm not familier by using functions in SQL. – Dreamcatcher_AR Feb 04 '20 at 09:42
  • Like I said, have a look at the [XML Answer](https://stackoverflow.com/a/15108499/2029983). If you fail to implement it, then post a new question, showing your attempt (**not** an image), and explain why it didn't work. – Thom A Feb 04 '20 at 09:45

1 Answers1

-1

This has probably already been answered on stackoverflow. Here for example: How to split a comma-separated value to columns

Depeneding on your version of sql server, there are probably better options than the accepted one in the question. Like for example using the built-in string_split function

Cedersved
  • 1,015
  • 1
  • 7
  • 21
  • The STRING_SPLIT function is not supported in SQL Server Management Studio 2018 – Dreamcatcher_AR Feb 04 '20 at 09:36
  • *"The STRING_SPLIT function is not supported in SQL Server Management Studio 2018"* `STRING_SPLIT` was added in SQL Server 2016; the version of SSMS you are using has nothing to do with it's availability, @AkshayRamesh . Your statement is completely wrong as the version of SSMS and whether you can use `STRING_SPLIT` has no relation. – Thom A Feb 04 '20 at 21:22