英文:
How to create a new column in SQL based on information in a column?
问题
我正在尝试在一个表格中查找消息代码，并认为如果我创建一个新列，用相应的公式指定列中的每个代码会更容易。
因此，我正在尝试在表格中创建一个名为MessageCode的新列，该列指定"MessageCode"之后的每个数字，但我不确定如何做到这一点。最终，我希望得到一个表格，基本上返回Id、Title、Enabled、Message Code和Actions。
如果您需要更多信息，请让我知道。此外，我正在使用SSMS。
ID Title Enabled Mark Colour Condition Action
1914 ABW: 012 1 -256 {"Operator":1,"Children":[{"Operator":1,"LeftOperand":}[{"ActionType":1,"Results":[{"ExcludeApplicationGroups":[5],"IncludeEZY":false,"StartDate":"2022-07-08T00:00:00","MessageCode":9109,"Ordinal":32,"Level":2,"Status":2,"LocalisedMessages":[{"Message":"<p>Passengers are advised to hold travel insurance that covers Covid-19.</p>"},{"LanguageId":50,"Message":"<p>Passagiers wordt aangeraden een reisverzekering af te sluiten die het Covid-19 dekt.</p>"},{"LanguageId":75,"Message":"<p><strong> </strong>Il est conseillé aux passagers d'avoir une assurance voyage qui couvre le Covid-19.</p>"},{"LanguageId":156,"Message":"<p><strong> </strong>Se aconseja a los pasajeros que tengan un seguro de viaje que cubra Covid-19.</p>"},{"LanguageId":140,"Message":"<p><strong> </strong>Os passageiros são aconselhados a possuir um seguro de viagem que cubra o Covid-19.</p>"},{"LanguageId":141,"Message":"<p><strong> </strong>Aconselha-se os passageiros a possuírem um seguro de viagem que cubra o Covid-19.</p>"}]}]}]
英文:
I am trying to search message codes within 1 table and think it would be easier if I create a new column that specifies each code within the column with the corresponding formula.
So I am trying to create a new column within the table called MessageCode which specifies each number after "MessageCode": but I am not sure how to do this. In the end I want a table that basically gives back Id, Title, Enabled, Message Code and Actions.
If you need further information please let me know.Also I am using SSMS.
ID Title Enabled Mark Colour Condition Action
1914 ABW: 012 1 -256 {"Operator":1,"Children":[{"Operator":1,"LeftOperand":} [{"ActionType":1,"Results":[{"ExcludeApplicationGroups":[5],"IncludeEZY":false,"StartDate":"2022-07-08T00:00:00","MessageCode":9109,"Ordinal":32,"Level":2,"Status":2,"LocalisedMessages":[{"Message":"<p>Passengers are advised to hold travel insurance that covers Covid-19.</p>"},{"LanguageId":50,"Message":"<p>Passagiers wordt aangeraden een reisverzekering af te sluiten die het Covid-19 dekt.</p>"},{"LanguageId":75,"Message":"<p><strong>&nbsp;</strong>Il est conseillé aux passagers d'avoir une assurance voyage qui couvre le Covid-19.</p>"},{"LanguageId":156,"Message":"<p><strong>&nbsp;</strong>Se aconseja a los pasajeros que tengan un seguro de viaje que cubra Covid-19.</p>"},{"LanguageId":140,"Message":"<p><strong>&nbsp;</strong>Os passageiros são aconselhados a possuir um seguro de viagem que cubra o Covid-19.</p>"},{"LanguageId":141,"Message":"<p><strong>&nbsp;</strong>Aconselha-se os passageiros a possuírem um seguro de viagem que cubra o Covid-19.</p>"}]}]}]
答案1
得分: 1
你可以尝试使用以下查询来读取“Action”列中的值：
SELECT ID, Title, Enabled, MarkColour, Condition,
JSON_VALUE(Action, '$[0].Results[0].MessageCode') AS MessageCode
FROM <YourTableName>
英文:
You can try to use the below query to read the value from Action column
SELECT ID, Title, Enabled, MarkColour, Condition,
JSON_VALUE(Action, '$[0].Results[0].MessageCode') AS MessageCode
FROM <YourTableName>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论