英文:
Separating One Column into Two Columns with Multiple Symbols
问题
我有一个包含CustomerPO和CustShippingOrder号码的列，它们通过-分隔并合并在一起：
PO
123456-7-89101112
我需要将这些列分解成这样：
Customer PO CustShippingOrder
--------------------------------------
1234567 89101112
数字7需要作为Customer PO列的一部分，我能够分隔这些列，但每次分隔后，-7-中的-仍然存在。
这是我当前的代码：
SELECT
CASE WHEN CHARINDEX('-',PO)>0 THEN SUBSTRING(PO,1,CHARINDEX('-',PO)-1)
ELSE PO END PO,
CASE WHEN CHARINDEX('-',PO)>0 THEN SUBSTRING(PO,CHARINDEX('-',PO)+1,len(PO)) ELSE NULL END AS [Customer PO]
有什么建议吗？
英文:
I have a column that contains the CustomerPO and CustShippingOrder numbers combined and separated by a -:
PO
123456-7-89101112
I need the columns to be broken up like this:
Customer PO CustShippingOrder
--------------------------------------
1234567 89101112
The 7 needs to be apart of the Customer PO column, I'm able to separate the columns but whenever I do, the - in the -7- stay.
This is the current code I have
SELECT
CASE WHEN CHARINDEX('-',PO)>0 THEN SUBSTRING(PO,1,CHARINDEX('-',PO)-1)
ELSE PO END PO,
CASE WHEN CHARINDEX('-',PO)>0 THEN SUBSTRING(PO,CHARINDEX('-',PO)+1,len(PO)) ELSE NULL END AS [Customer PO]
Any suggestions?
答案1
得分: 2
parsename() 看起来在这里很合适。
示例
声明 @YourTable 表（somecol varchar(50))
向 @YourTable 插入数据
('123456-7-89101112')
从 @YourTable A 中选择 A.SomeCol
,NewVal1 = parsename(NewStr,3)+parsename(NewStr,2)
,NewVal2 = parsename(NewStr,1)
通过交叉应用（ values ( replace(SomeCol,'-','.') ) )B(NewStr)
结果
SomeCol NewVal1 NewVal2
123456-7-89101112 1234567 89101112
英文:
parsename() seems like a good fit here
Example
Declare @YourTable table (somecol varchar(50))
Insert Into @YourTable values
('123456-7-89101112')
Select A.SomeCol
,NewVal1 = parsename(NewStr,3)+parsename(NewStr,2)
,NewVal2 = parsename(NewStr,1)
From @YourTable A
Cross Apply ( values ( replace(SomeCol,'-','.') ) )B(NewStr)
Results
SomeCol NewVal1 NewVal2
123456-7-89101112 1234567 89101112
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论