SQL Server表中的定界符 [英] Delimiter in SQL Server table

问题描述

你好，
我会寻求帮助以解决我的问题...
我在SQL Server数据库中有代表层次结构的表...
此外，还有一列包含#" singn作为级别的分隔符...
这是专栏的样子:

Hello,
I would apprecite help in order to solve my problem...
I have table in SQL Server db representing hierarchy...
Also, there is column which contains ''#'' singn as delimiter for the level...
Here is how column looks like:

All Values
#Loans
##Revolving
###Credit Cards

我想制作一个函数，将这个列转换为4个单独的列(使用#"作为定界符)，结果如下:

I would like to make function that will transform this column in 4 separated colums (using ''#'' as delimiter) with the result as follows:

Column1       Column2       Column3     Column4
All Values
              Loans
                            Revolving
                                        Credit Cards

有人可以帮我吗?
预先感谢您的时间和精力.

此致

Can someone please help me with this?
Thanks in advance for your time and effort.

Regards,

推荐答案

DECLARE @Table1 AS TABLE(rwid bigint ,val  NVARCHAR(MAX))
DECLARE @coun as bigint
DECLARE @x as bigint
DECLARE @Table2 AS TABLE(rwid bigint identity(1,1),column1 NVARCHAR(255),column2 NVARCHAR(255),column3 NVARCHAR(255),column4 NVARCHAR(255))
DECLARE @col1 AS NVARCHAR(255)
DECLARE @col2 AS NVARCHAR(255)
DECLARE @col3 AS NVARCHAR(255)
DECLARE @Col4 AS NVARCHAR(255)
DECLARE @nextlevelQuilifier as bigint
DECLARE @Status AS NVARCHAR(50)

SET @x=1
SET @nextlevelQuilifier=1

INSERT INTO @Table1 
SELECT 1,'All Values'
Union
SELECT 2,'#Loans'
Union
SELECT 3,'##Revolving'
Union
SELECT 4,'###Credit Cards'

  

SELECT @coun=COUNT(*) FROM @Table1
 
While @coun >= @x
 Begin
        SELECT @nextlevelQuilifier=[dbo].[ufn_CountChar](val,'#') FROM @Table1 WHERE rwid=@x 
   		
		IF  @nextlevelQuilifier=0 
			BEGIN
				SELECT @col1= REPLACE(Val,'#','') FROM  @Table1 WHERE rwid=@x 
				INSERT INTO @Table2(column1,column2,column3,column4) 
				SELECT @col1 ,'','' ,''				
			END

		ELSE IF  @nextlevelQuilifier=1
			BEGIN
				SELECT @col2= REPLACE(Val,'#','') FROM  @Table1 WHERE rwid=@x 
				INSERT INTO @Table2 (column1,column2,column3,column4)
				SELECT '' ,@col2 ,'' ,'' 
			END

		ELSE IF  @nextlevelQuilifier=2 
			BEGIN
				SELECT @col3= REPLACE(Val,'#','') FROM  @Table1 WHERE rwid=@x 
				INSERT INTO @Table2 (column1,column2,column3,column4)
				SELECT '','',@col3 ,''
			END

		ELSE IF  @nextlevelQuilifier=3 
			BEGIN
				SELECT @col4= REPLACE(Val,'#','') FROM  @Table1 WHERE rwid=@x 
				INSERT INTO @Table2 (column1,column2,column3,column4)
				SELECT '','','',@Col4 
			END

set @x=@x+1
        	  
 End

SELECT * from @Table2 order by rwid

为此，您需要下面的函数来计算字符串中的char

For this you need below function for counting char in string

CREATE FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar NVARCHAR(255) )
RETURNS INT
BEGIN

DECLARE @vInputLength        INT
DECLARE @vIndex              INT
DECLARE @vCount              INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = LEN(@pInput)

WHILE @vIndex <= @vInputLength
BEGIN
    IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchChar
        SET @vCount = @vCount + 1

    SET @vIndex = @vIndex + 1
END

RETURN @vCount

END
GO

希望这对您有帮助，请接受并投票，否则请返回您的查询
--Rahul D.

Hope this helps if yes then please accept and vote the answer otherwise revert back with your queries
--Rahul D.

我知道这不是您要的，但是自从您用SQL2008标记问题以来，您是否给出过 ^ ]有什么想法吗?本文介绍了如何使用标准T-SQL手段对层次结构进行建模，然后继续演示如何使用层次结构ID来实现相同的方法.

问候，

曼弗雷德(Manfred)

I know this is not what you asked for, but since you labled your question with SQL2008 have you given Hierarchy ID[^] any thought yet? The article explains how to model a hierarchy using standard T-SQL means and then goes on to demonstrate how to implement the same using a hierarchy id.

Regards,

Manfred

这篇关于SQL Server表中的定界符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！