sql server如何分隔全名

本文介绍了sql server如何分隔全名的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我不善于提出问题

i希望在很多专栏中分隔全名（ABCBEF）我这个代码要做到这一点

i'm not good in asking a questions

i want to separate fullname ( A B C B E F ) in many column i us this code to do that

SELECT SUBSTRING(Name_Arabic, 1, CASE WHEN CHARINDEX(' ', Name_Arabic) = 0 THEN len(Name_Arabic) ELSE CHARINDEX(' ', Name_Arabic) END) name1,REPLACE(SUBSTRING(Name_Arabic, CASE WHEN CHARINDEX(' ',Name_Arabic) = 0 THEN len(Name_Arabic) ELSE CHARINDEX(' ', Name_Arabic) END + 1, LEN(Name_Arabic)), REVERSE(SUBSTRING(REVERSE(Name_Arabic), 1, CHARINDEX(' ', REVERSE(Name_Arabic)))), '') name2, 
                      REVERSE(SUBSTRING(REVERSE(Name_Arabic), 1, CHARINDEX(' ', REVERSE(Name_Arabic)))) name3
FROM            tabl DROP TABLE tabl

例子（ahmed ali adham gaber sumer艾哈迈德）

它给了我（column1 = ahmed，c olumn2 = ali adham gaber sumer，column3 = ahmed）

我要找的是（column1 = ahmed，column2 = ali，column3 = adham，column4 = gaber，column5 = sumer，column6 = ahmed）

我该怎么办

我有什么试过：

i搜索那个并没有发现任何东西

example ( ahmed ali adham gaber sumer ahmed )
and it gave me ( column1 = ahmed , column2= ali adham gaber sumer, column3= ahmed )

what i looking for is (column1= ahmed , column2= ali , column3= adham , column4= gaber ,column5= sumer , column6= ahmed)

how can i do that

What I have tried:

i search about that and didn't find any thing

推荐答案

CREATE FUNCTION CustomStringSplit(@value nvarchar(max), @delimiter nvarchar(max)) 
RETURNS @items TABLE (
   Ordinal	int,
   Part     nvarchar(max)
) AS 
BEGIN
   WITH Items (Ordinal, Part, Remainder) AS (
      SELECT 1 AS Ordinal,
			 CASE
                WHEN CHARINDEX(@delimiter, @value) > 0 THEN LEFT(@value, CHARINDEX(@delimiter, @value) - 1)
		        ELSE @value
		  END AS Part,
		  CASE
             WHEN CHARINDEX(@delimiter, @value) > 0 THEN SUBSTRING(@value, CHARINDEX(@delimiter, @value) + 1, 99999999)
		     ELSE NULL
		  END AS Remainder
      UNION ALL
	  SELECT Items.Ordinal + 1 AS Ordinal,
	         CASE
                WHEN CHARINDEX(@delimiter, Remainder) > 0 THEN LEFT(Remainder, CHARINDEX(@delimiter, Remainder) - 1)
		        ELSE Remainder
		     END AS Part,
		     CASE
                WHEN CHARINDEX(@delimiter, Remainder) > 0 THEN SUBSTRING(Remainder, CHARINDEX(@delimiter, Remainder) + 1, 99999999)
		        ELSE NULL
		     END AS Remainder
	   FROM Items
	   WHERE Items.Remainder IS NOT NULL
   )
   INSERT INTO @items (Ordinal, Part) 
      SELECT Items.Ordinal, Items.Part FROM Items;

   RETURN;
END;

创建上述函数后，您可以尝试将其与

After creating the function above, you can try to use it with

DECLARE @name varchar(100);
SET @name = 'ahmed ali adham gaber sumer ahmed';

SELECT * FROM CustomStringSplit(@name, ' ');

结果是

The result is

Ordinal	Part
------- -----
1       ahmed
2       ali
3       adham
4       gaber
5       sumer
6       ahmed

为了将数据作为列获取你可以尝试例如

In order to get the data as columns you could try for example

DECLARE @name varchar(100);
SET @name = 'ahmed ali adham gaber sumer ahmed';
SELECT @name, 
       (SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 1) AS Col1,
       (SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 2) AS Col2,
       (SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 3) AS Col3,
       (SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 4) AS Col4,
       (SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 5) AS Col5,
       (SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 6) AS Col6,
       (SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 7) AS Col7,
       (SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 8) AS Col8,
       (SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 9) AS Col9,
       (SELECT Part FROM CustomStringSplit(@name, ' ') WHERE Ordinal = 10) AS Col10

结果应为

The result should be

(No column name)	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10
--------------------------------------------------------------------
ahmed ali adham gaber sumer ahmed	ahmed	ali	adham	gaber	sumer	ahmed	NULL	NULL	NULL	NULL

如果你正在使用SQL Server 2016，你也可以尝试使用 []而不是自定义函数。

If you're using SQL Server 2016, you could also try using STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^] instead of the custom function.

这篇关于sql server如何分隔全名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Ahmed