英文:
How To Add White Space Between Text and Numeric
问题
我想要将这些值添加一个空格,像这样:
D 8565 GL
PA 8791 AG
我该如何在SQL Server 中解决这个问题?感谢。
英文:
I have an ID column in my table which contains values like this:
D8565GL
PA8791AG
I want to add a white space to these values like this:
D 8565 GL
PA 8791 AG
How do I solve this using SQL Server?
Thank you
答案1
得分: 2
I'm assuming that all your ID values follow a similar format of essentially /[alpha]+[digit]+[alpha]+/
Despite that assumption, the code below is actually only relying on there being two transitions in the string where the character sequence switches between numeric to alphabetic and then again the opposite way. The order that happens (left to right) doesn't matter so effectively it also handles /^[:alpha:]+[:digit:]+[:alpha:]*$/
and /^[:digit:]+[:alpha:]+[:digit:]*$/
You can easily extend this pattern if necessary.
select *
from T
cross apply
(values (stuff(ID, patindex('%[A-Z][0-9]%', ID) + 1, 0, ' '))) as v1(ID2)
cross apply
(values (stuff(ID2, patindex('%[0-9][A-Z]%', ID2) + 1, 0, ' '))) as v2(NewID);
If no pattern matches are found then it ends up adding a space character in the initial position. A simple ltrim()
would clean that up.
英文:
I'm assuming that all your ID values follow a similar format of essentially /[alpha]+[digit]+[alpha]+/
Despite that assumption, the code below is actually only relying on there being two transitions in the string where the character sequence switches between numeric to alphabetic and then again the opposite way. The order that happens (left to right) doesn't matter so effectively it also handles /^[:alpha:]+[:digit:]+[:alpha:]*$/
and /^[:digit:]+[:alpha:]+[:digit:]*$/
You can easily extend this pattern if necessary.
select *
from T
cross apply
(values (stuff(ID, patindex('%[A-Z][0-9]%', ID) + 1, 0, ' '))) as v1(ID2)
cross apply
(values (stuff(ID2, patindex('%[0-9][A-Z]%', ID2) + 1, 0, ' '))) as v2(NewID);
If no pattern matches are found then it ends up adding a space character in the initial position. A simple ltrim()
would clean that up.
答案2
得分: 1
不需要翻译的部分: select concat(
, from vehicles;
, Quick fix I cooked [here](https://dbfiddle.uk/kEWZKb3N). It uses regex to find the 4 digits location and then just do substring to get the values.
需要翻译的部分:
substring(ID, 0, patindex('%[0-9]%', ID)),
' ',
substring(ID, patindex('%[0-9]%', ID), 4),
' ',
substring(ID, patindex('%[0-9]%', ID) + 4, 2)
翻译后的部分:
substring(ID, 0, patindex('%[0-9]%', ID)),
' ',
substring(ID, patindex('%[0-9]%', ID), 4),
' ',
substring(ID, patindex('%[0-9]%', ID) + 4, 2)
英文:
select concat(
substring(ID, 0, patindex('%[0-9]%', ID)),
' ',
substring(ID, patindex('%[0-9]%', ID), 4),
' ',
substring(ID, patindex('%[0-9]%', ID) + 4, 2)
)
from vehicles;
Quick fix I cooked here. It uses regex to find the 4 digits location and then just do substring to get the values.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论