在文本和数字之间添加空格

huangapple go评论143阅读模式
英文:

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.

https://dbfiddle.uk/d06S7YSG

答案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.

huangapple
  • 本文由 发表于 2023年3月7日 13:20:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/75658279.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定