String Manipulation:获取角色的索引并获取下一个角色 [英] String Manipulation: Getting index of a character and getting next characters
问题描述
您好。我在SQL SERVER 2008中遇到字符串操作问题
这只在一个字段中,我需要将其提取到FIELD NAME和VALUE。
字段名称是冒号和分号之间的名称(这些是大写字母)
字段值是冒号后和下一个分号前的值。
请帮帮我
BENEACCTNAME:Juan Dela Cruz; BENEACCT:0123456; BENEBNKNAME:虚拟银行; BENEBNKADD:123 32nd Ave,Earth; BENEBNKMET:BCD; BENEBNKCODE:1111133333; BENEBNKCC:AR; INTBNKNAME :; INTBNKADD :; INTBNKMET :; INTBNKCODE :; PAYDET1:DetailsAcc; PAYDET2 :; PAYDET3 :; PAYDET4 :; BNKTOBNKINFO1 :; BNKTOBNKINFO2 :; BNKTOBNKINFO3 :; BNKTOBNKINFO4 :; BNKTOBNKINFO5 :; BNKTOBNKINFO6 :; BENEBNKADD1 :; BENEBNKADD2 :; BENEBNKADD3:;
这是假定的输出
BENEACCTNAME | BENEACCT | BENEBNKNAME | BENEBNKADD | BENEBNKMET
Juan Dela Cruz | 0123456 |虚拟银行| 123 32nd Ave,Earth | BCD
等...
以下是根据您的要求提供的完整源代码。
它在Dictionary中存储名称,值对。
Dictionary< string,string> dict = new 字典< string,string>();
字符串 s = BENEACCTNAME:Juan Dela Cruz; BENEACCT:0123456; BENEBNKNAME:Dummy Bank; +
BENEBNKADD:123 32nd Ave,Earth; BENEBNKMET:BCD; BENEBNKCODE:1111133333; +
BENEBNKCC:AR ; INTBNKNAME:; INTBNKADD:; INTBNKMET:; INTBNKCODE:; PAYDET1:DetailsAcc; +
PAYDET2:; PAYDET3:; PAYDET4:; BNKTOBNKINFO1:; BNKTOBNKINFO2:; BNKTOBNKINFO3:; BNKTOBNKINFO4:; +
BNKTOBNKINFO5:; BNKTOBNKINFO6:; BENEBNKADD1:; BENEBNKADD2:; BENEBNKADD3:;;
var split = s.Split(' ;'跨度>);
foreach ( var item in split)
{
if (!string.IsNullOrEmpty(item.Trim()))
{
< span class =code-keyword> string name = item.Split(' :')[ 0 ];
string value = item.Split(' :')[ 1 ];
dict.Add(name, value );
}
}
这是SQL Server:
< pre lang =SQL> 声明 @输入 NVARCHAR (MAX)
set @ Input = ' BENEACCTNAME:Juan Dela Cruz; BENEACCT:0123456; BENEBNKNAME:虚拟银行; BENEBNKADD:123 32nd Ave,Earth; BENEBNKMET:BCD; BENEBNKCODE:1111133333; BENEBNKCC:AR; INTBNKNAME :; INTBNKADD :; INTBNKMET :; INTBNKCODE :; PAYDET1:DetailsAcc; PAYDET2 :; PAYDET3 :; PAYDET4 :; BNKTOBNKINFO1 :; BNKTOBNKINFO2 :; BNKTOBNKINFO3 :; BNKTOBNKINFO4 :; BNKTOBNKINFO5 :; BNKTOBNKINFO6 :; BENEBNKADD1 :; BENEBNKADD2 :; BENEBNKADD3:;'
DECLARE @输出 表(
name NVARCHAR ( 100 ) ,
值 NVARCHAR ( 100 )
)
DECLARE @ StartIndex INT , @ EndIndex INT , @ name NVARCHAR ( 100 ), @ value NVARCHAR ( 100 ), @ Character CHAR ( 1 )
SET @ Character = ' ;'
SET @ StartIndex = 1
IF SUBSTRING( @ Input ,LEN( @输入) - 1 ,LEN( @ Input ))<> ; @ Character
BEGIN
SET @输入 = @输入 + @ Character
END
WHILE CHARINDEX( @ Character , @ Input )> 0
BEGIN
SET @ EndIndex = CHARINDEX( @ Character , @输入)
IF ( @ EndIndex > 1 )
BEGIN
声明 @ index int
set @ index = CHARINDEX(' :', @输入)
SET @ name = SUBSTRING( @ Input , @ StartIndex , @ index - 1 )
SET @ value = SUBSTRING( @ Input , @ index + 1 , @ EndIndex - @ index - 1 )
INSERT INTO @输出(名称,值) VALUES ( @ name , @ value )
END
SET @ Input = SUBSTRING( @ Input , @ EndIndex + 1 ,L EN( @ Input ))
END
从 @输出 >
问候,
Saad
Hi. I'm having a problem in string manipulation in SQL SERVER 2008
This is in one field only and I need to extract this into FIELD NAME and VALUE.
Field names are those in between colon and semi-colon (these are the capital letters)
Field values are those after the colon and before the next semi-colon.
Please help me
BENEACCTNAME:Juan Dela Cruz; BENEACCT:0123456; BENEBNKNAME:Dummy Bank; BENEBNKADD:123 32nd Ave, Earth; BENEBNKMET:BCD; BENEBNKCODE:1111133333; BENEBNKCC:AR; INTBNKNAME:; INTBNKADD:; INTBNKMET:; INTBNKCODE:; PAYDET1:DetailsAcc; PAYDET2:; PAYDET3:; PAYDET4:; BNKTOBNKINFO1:; BNKTOBNKINFO2:; BNKTOBNKINFO3:; BNKTOBNKINFO4:; BNKTOBNKINFO5:; BNKTOBNKINFO6:; BENEBNKADD1:; BENEBNKADD2:; BENEBNKADD3:;
this is the supposed output
BENEACCTNAME | BENEACCT | BENEBNKNAME | BENEBNKADD | BENEBNKMET
Juan Dela Cruz | 0123456 | Dummy Bank | 123 32nd Ave, Earth | BCD
and so on...
Here is the complete source code according to your requirements.
It store the name, value pair in Dictionary.
Dictionary<string, string> dict = new Dictionary<string, string>(); String s = "BENEACCTNAME:Juan Dela Cruz; BENEACCT:0123456; BENEBNKNAME:Dummy Bank; " + "BENEBNKADD:123 32nd Ave, Earth; BENEBNKMET:BCD; BENEBNKCODE:1111133333; " + "BENEBNKCC:AR; INTBNKNAME:; INTBNKADD:; INTBNKMET:; INTBNKCODE:; PAYDET1:DetailsAcc; " + "PAYDET2:; PAYDET3:; PAYDET4:; BNKTOBNKINFO1:; BNKTOBNKINFO2:; BNKTOBNKINFO3:; BNKTOBNKINFO4:; " + "BNKTOBNKINFO5:; BNKTOBNKINFO6:; BENEBNKADD1:; BENEBNKADD2:; BENEBNKADD3:;"; var split = s.Split(';'); foreach (var item in split) { if (!string.IsNullOrEmpty(item.Trim())) { string name = item.Split(':')[0]; string value = item.Split(':')[1]; dict.Add(name, value); } }
Here is SQL Server:
declare @Input NVARCHAR(MAX) set @Input = 'BENEACCTNAME:Juan Dela Cruz; BENEACCT:0123456; BENEBNKNAME:Dummy Bank; BENEBNKADD:123 32nd Ave, Earth; BENEBNKMET:BCD; BENEBNKCODE:1111133333; BENEBNKCC:AR; INTBNKNAME:; INTBNKADD:; INTBNKMET:; INTBNKCODE:; PAYDET1:DetailsAcc; PAYDET2:; PAYDET3:; PAYDET4:; BNKTOBNKINFO1:; BNKTOBNKINFO2:; BNKTOBNKINFO3:; BNKTOBNKINFO4:; BNKTOBNKINFO5:; BNKTOBNKINFO6:; BENEBNKADD1:; BENEBNKADD2:; BENEBNKADD3:;' DECLARE @Output TABLE ( name NVARCHAR(100), value NVARCHAR(100) ) DECLARE @StartIndex INT, @EndIndex INT, @name NVARCHAR(100), @value NVARCHAR(100), @Character CHAR(1) SET @Character = ';' SET @StartIndex = 1 IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character BEGIN SET @Input = @Input + @Character END WHILE CHARINDEX(@Character, @Input) > 0 BEGIN SET @EndIndex = CHARINDEX(@Character, @Input) IF(@EndIndex > 1) BEGIN declare @index int set @index = CHARINDEX(':', @Input) SET @name = SUBSTRING(@Input, @StartIndex, @index - 1) SET @value = SUBSTRING(@Input, @index + 1, @EndIndex - @index - 1) INSERT INTO @Output(name, value) VALUES (@name, @value) END SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input)) END select * from @Output
Regards,
Saad
这篇关于String Manipulation:获取角色的索引并获取下一个角色的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!