SQL Server:按“/"拆分字符串并将每个拆分元素放入不同的列中 [英] SQL Server: Splitting string by '/' and put each split element into a different column
问题描述
我在外部站点上有一个表,我需要将其复制到本地数据库,但需要进行一些转换.我必须大量修改的列之一称为 product_url.URL 的格式为 site.com\category\sub-category\brand\model#.
I have a table on an external site that I need to copy to a local DB, but with some transformations. One of the columns I have to heavily modify is called product_url. The URL is in the form site.com\category\sub-category\brand\model#.
我本地数据库中的表将有 4 列来保存这些数据.它们将是类别、子类别、品牌和型号#.因此,我必须首先修剪 site.com(我使用 truncate 完成此操作),但现在我必须解析 \category\sub-category\brand\model#
The table in my local db will have 4 columns to hold this data. They will be category, sub-category, brand and model#. So, I will have to first trim the site.com (I did this using truncate), but now I have to parse \category\sub-category\brand\model#
我从 SO 找到了一个 UDF,我认为它会有所帮助.这是:
I found a UDF from SO that I think will help out. Here it is:
create function dbo.SplitString
(
@str nvarchar(4000),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
1,
1,
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 zeroBasedOccurance,
substring(
@str,
a,
case when b > 0 then b-a ELSE 4000 end)
AS s
from tokens
)
GO
现在我在使用此功能时遇到问题.可能是因为我缺乏 UDF 的经验.
Now I am having trouble using this function. Probably due to my lack of experience with UDF's.
这是我现在所拥有的:
select s from
dbo.SplitString(select substring(product_url, 8, len(product_url))
from Products, '/')
where zeroBasedOccurance=0 AS Category
这显然在语法上也不正确.
This is obviously not even syntactically correct.
我想知道我是否以最好的方式解决这个问题.我还不是一个 DBA,所以我很难解决这个问题.我只需要弄清楚如何为 product_url 表中的每一行应用这个 UDF ~4 次.
I am wondering if I am going about this the best way. I am not yet much of a DBA, so I am having a hard time wrapping my head around this issue. I just need to figure out how to apply this UDF ~4 times for each row in the product_url table.
推荐答案
不确定你的功能,但这是我的:
Not sure about your function, but here is mine:
CREATE FUNCTION dbo.FN_PARSENAME(@chunk VARCHAR(4000), @delimiter CHAR(1), @index INT )
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE
@curIndex INT = 0,
@pos INT = 1,
@prevPos INT = 0,
@result VARCHAR(1000)
WHILE @pos > 0
BEGIN
SET @pos = CHARINDEX(@delimiter, @chunk, @prevPos);
IF(@pos > 0)
BEGIN -- Characters between position and previous position
SET @result = SUBSTRING(@chunk, @prevPos, @pos-@prevPos)
END
ELSE
BEGIN -- Last Delim
SET @result = SUBSTRING(@chunk, @prevPos, LEN(@chunk))
END
IF(@index = @curIndex)
BEGIN
RETURN @result
END
SET @prevPos = @pos + 1
SET @curIndex = @curIndex + 1;
END
RETURN '' -- Else Empty
END
你这样称呼它:SELECT Address_Line1 = dbo.fn_Parsename(Merged,'|', 0)从表
其中Merged是被分隔的字段,'|'是分隔符,因此您可以将其设为\",0 是您想要的字符串部分,0 是第一个,向上.
Wherein Merged is the field that is delimited, the '|' is the delimiter, so you would make it '\', and the 0 is which portion of the string you want, 0 being the first, on up.
对于您的示例,它将是:
For your example it would be:
SELECT category = dbo.fn_Parsename(product_url,'\', 1)
, sub-category = dbo.fn_Parsename(product_url,'\', 2)
, brand = dbo.fn_Parsename(product_url,'\', 3)
, model# = dbo.fn_Parsename(product_url,'\', 4)
FROM Table
或者可能是 0-3 取决于.
Or maybe 0-3 depending.
我非常有信心根据我发现的东西改编它,甚至可能是在 SO 上,但我不记得谁应该受到赞扬.
I'm pretty confident I adapted that from something I found, perhaps even on SO, but I can't recall who would deserve credit.
这篇关于SQL Server:按“/"拆分字符串并将每个拆分元素放入不同的列中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!