Sql server how to separate full name

Question

0.00/5 (No votes)

See more:

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

SQL

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

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

Posted 24-Dec-18 20:52pm

el_tot93

Updated 25-Dec-18 2:03am

v2

Add a Solution

Comments

Wendelius 25-Dec-18 3:23am

Can you post some examples of the names you're trying to separate and for each example what the result should be?

el_tot93 25-Dec-18 3:43am

i update my question

Mohibur Rashid 25-Dec-18 3:59am

What's your maximum column count? What is your table schema, one thing is you can't have dynamic table schema. if you need dynamic table format then SQL is not the best solution

el_tot93 25-Dec-18 4:08am

the maximum column is (15) ..
i just want to separate full name

2 solutions

Solution 2

You can also use the "XML approach" described here:
SQL User Defined Function to Parse a Delimited String[^]
This seems to be more efficient according to the author.

Posted 25-Dec-18 2:04am

RickZeeland

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2018-12-24T22:08:00

As pointed out in the comments, SQL is a bit difficult language for dynamic amount of columns. Having that said, you could split the string into multiple rows and then select individual names.

One way to do this is to create a custom function. Consider this example

SQL

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

SQL

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

SQL

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

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.