I need to delimit @uid by "-". The issue is my data set has "--1" and I need it be treated as "-1"
I need @uid = '1585-1586--1-5417-2347-8865' to output this:
Instead of:
How can I achieve this in SQL?
I need to delimit @uid by "-". The issue is my data set has "--1" and I need it be treated as "-1"
I need @uid = '1585-1586--1-5417-2347-8865' to output this:
Instead of:
How can I achieve this in SQL?
3条答案
按热度按时间vaj7vani1#
The answer you have helps you a little here, however, with no definition of
[dbo].[fnSplit]
doesn't help any one else.If we can assume that the data is well defined (has 6 columns), then we could "spam" some
CHARINDEX
functions to do this. You will, as shown in the answer, need to replace all the delimiters and then reinsert the value of-
for the double delimiter:Of course, if you had a "empty" value, then this will fail:
Invalid length parameter passed to the LEFT or SUBSTRING function.
And hence why a delimiter than can appear in your data should never be used (however, one can hope that as these all appear to be integer values then a
NULL
value wouldn't exist and there would be a0
instead:'1585-0-71-5417-2347-8865'
).If you used a string splitter like
DelimitedSpluit8K_LEAD
then you could Pivot (and unpivot) the data fine, but the values would be in the wrong positions with the above example:Which will result in the below:
mrzz3bfm2#
Basically what I'm doing is a recursive cte from 6 to 1. each iteration I am removing the last delimited number and moving it to
col_val
column. I decided to usereverse
so that I could then usepatindex
to find the hyphen then the number. Doing that made it possible to get the negative values. In reverse the string looks like 1--6851-5851-0 thenpatindex('%-[0-9]%', <string>)
returns 2 and because I usedright
function of the string 0-1585-1586--1 it will return -1I added
'0-'
to the beginning of thedelim_column
because I want to usepatindex
without having to account for the last delimited column.The column
col_val
is repeating all the above but instead of using@uid
it is usingdelim_column
Here is what each iteration looks like:
Then I'm pivoting the columns using a simple choose function. That will make the column names clean.
shyt4zoc3#
使用下面的脚本可以获得所需的结果。该脚本依赖于名为[fnSplit]的用户定义函数。[fnSplit] UDF将在后面的文章中定义。
第一个