0

I have a concatenated column(order_type) like this:

     OrderType
Data;Let;Data;Data;Let

I want to create two columns from the above column

Data Let
 3    2
bot9123
  • 51
  • 5
  • 5
    That's the problem when you decide to use a concatenated column for data - you have to jump through hoops every time you want to do something with that data. That's why concatenated columns are considered bad table design and should be avoided like the plague. – Ken White Oct 21 '21 at 16:47
  • No it doesn't. I know how to delimit the above column from the semi colons. I need to add the number of Data and Let in new columns. – bot9123 Oct 21 '21 at 16:59
  • 4
    Your database design is at 0NF level, i.e. is non-normalized. This is an artificial problem that you can easily avoid by normalizing the database. – The Impaler Oct 21 '21 at 17:08
  • @bot9123 That is information that should have been included in the question. Please [edit] your question to make that explicit, and add any other information that we would need to help you - for example, is the data always the exact strings "Data" and "Let"? If not, then could the column names exceed the maximum number of column names allowed in a table? – Andrew Morton Oct 21 '21 at 19:48

1 Answers1

1

Fixing your data model, while the correct approach, probably can't be done immediately so a hacky workaround is to just use some simple string processing:

select 
 (length(ordertype) - length(Replace(ordertype,'Data',''))) / Length('Data') as Data,
 (length(ordertype) - length(Replace(ordertype,'Let',''))) / Length('Let') as Let
from t
Stu
  • 30,392
  • 6
  • 14
  • 33