1

Had a bit of a search but couldnt find an answer to this problem. String manipulation is not my strong suit.

I have the following example string.

7849;7985;8044;8253;8254;16733

I need to count the number of 'Ids' in the string. Using the above example it should come out as 6 because each set of numbers between the semi colon represents one ID.

Any help is appreciated.

Nick Jones
  • 93
  • 8

1 Answers1

2

You can count the number of semicolons using string functions and then add one:

select (length(str) - length(replace(str, ';', '')) + 1 as num_elements

That said, you should fix your data model so you are not storing multiple values in a string. These should be on separate rows of another table.

If you are not using MySQL, some databases use LEN() instead of LENGTH().

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks for response Gordon, its not working though. It does alright up to 4 but any that are 6 or greater it just stops at 7, using the following. 7809;7850;7890;7917;7938;7986;8045;8072;15149 - This only gives 7. Using the following code len(CAST(A.U_AceqCodes AS VARCHAR)) - len(replace(CAST(A.U_AceqCodes AS VARCHAR), ';', '')) + 1 Unfortunately the data is coming from an addon within SAP B1 and I dont have control of the data model. – Nick Jones Jun 03 '21 at 00:58
  • @NickJones . . . (1) That is not the code in this answer. (2) That is not valid MySQL syntax. – Gordon Linoff Jun 03 '21 at 01:04
  • Using your code above I get The replace function requires 3 arguments – Nick Jones Jun 03 '21 at 01:10
  • All working now, the problem was when I was casting the field to varchar i didnt include MAX so it was limiting it. – Nick Jones Jun 03 '21 at 01:24