sql query or stored procedure to break string

Question

1.00/5 (1 vote)

See more:

Dear friends,

Suppose i have a table named as employee having column empname.It contains data as

1.Harry potter
2.Steve john

My requirement is

1.Harry
2.potter
3.Steve
4.john

Thanks

Posted 8-Sep-11 0:59am

3796068

Updated 8-Sep-11 1:00am

v2

Add a Solution

Comments

Corporal Agarn 8-Sep-11 7:06am

What have you come up with so far?

Sandeep Mewara 27-Sep-12 12:31pm

Here is what is expected of enquirers:
1. TRY first what you want to do! You may find that it's not that hard.
2. Formulate what was done by you that looks like an issue/not working.

Try them and tell if you face issues.
Members will be more than happy to help like this.

4 solutions

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)

Tejas Vaishnav · Answer 1 · 2011-09-08T01:29:00

Solution 1

Hello Friend Try this...

SQL

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TestTable') AND type in (N'U'))
DROP TABLE TestTable
GO
CREATE TABLE TestTable(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name Nvarchar(20)
)


INSERT INTO TestTable(Name) VALUES ('Tejas Vaishnav')
INSERT INTO TestTable(Name) VALUES ('Harry Potter')
INSERT INTO TestTable(Name) VALUES ('Steve john')

SELECT * FROM TestTable 

--this will result in this
--1	Tejas Vaishnav
--2	Harry Potter
--3	Steve john

SELECT SUBSTRING(Name, 1,CHARINDEX(' ', Name)) AS FIRSTNAME,SUBSTRING(Name, CHARINDEX(' ', Name),LEN(Name)) AS LASTNAME
FROM TestTable

--this will result in this
--1  Tejas Vaishnav
--2  Harry Potter
--3  Steve john

You Can use it like query and also in storeporcedure also.

Posted 8-Sep-11 1:29am

Tejas Vaishnav

Comments

Strange_Pirate 27-Sep-12 7:30am

CREATE FUNCTION SplitString
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)

--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0

Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

Insert Into @ReturnTable(part)
Select @part

Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))

Select @iSpaces = charindex(@deliminator,@myString,0)
end

If len(@myString) > 0
Insert Into @ReturnTable
Select @myString

RETURN
END
GO

select * From SplitString('Hello**Strange**Pirate','**')

Strange_Pirate 27-Sep-12 7:30am

The solution is not quite handy and reusabale. ?

Tejas Vaishnav 27-Sep-12 7:35am

I just try to help him out, if we provide the whole thing as ready mate then that person have no idea how it works. and also he/she become lazy to work with their own self. so i always provide hints not direct solution so they can resolve their problem by them selves so if in future they get any problem regarding this then they can handle it easily.

Strange_Pirate 27-Sep-12 7:46am

Is it Your Personal Profile ?? B'Coz you are doing:)

Sorry gal not agree with you..!!

And one thing i am not understanding about your post is that the, What is "READY MATE"..!!;)
Gotcha...!!;)

Chears.. You did a great job here.:) Salute

Strange_Pirate 27-Sep-12 7:48am

Ok !!
Agreed, that you are giving just Hints. but the hint given by you meets users requirement .? ;) Naaahhhhh!!

Akshaya Dash · Answer 2 · 2011-09-08T01:32:00

Solution 2

have you check this,

http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-recor[^]

http://stackoverflow.com/questions/2647/split-string-in-sql[^]

Posted 8-Sep-11 1:32am

Akshaya Dash

damodara naidu betha · Answer 3 · 2012-09-27T03:22:00

Solution 4

Hi friend,

try this following code block...

SQL

WITH Namescte AS (
    SELECT  CAST('<i>' + REPLACE(Name, ' ', '</i><i>') + '</i>' AS XML) AS Names
    FROM YourTable
)
SELECT 
    x.i.value('.', 'VARCHAR(10)') AS Names
FROM Namescte
CROSS APPLY Names.nodes('//i') x(i)

Posted 27-Sep-12 3:22am

damodara naidu betha

Updated 27-Sep-12 18:43pm

v6

Strange_Pirate · Answer 4 · 2012-09-27T01:41:00

Hey Coder, First create a function as below

SQL

CREATE FUNCTION SplitString 
(
    -- Add the parameters for the function here
    @myString varchar(500),
    @deliminator varchar(10)
)
RETURNS 
@ReturnTable TABLE 
(
    -- Add the column definitions for the TABLE variable here
    [id] [int] IDENTITY(1,1) NOT NULL,
    [part] [varchar](50) NULL
)
AS
BEGIN
        Declare @iSpaces int
        Declare @part varchar(50)

        --initialize spaces
        Select @iSpaces = charindex(@deliminator,@myString,0)
        While @iSpaces > 0

        Begin
            Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

            Insert Into @ReturnTable(part)
            Select @part

    Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))


            Select @iSpaces = charindex(@deliminator,@myString,0)
        end

        If len(@myString) > 0
            Insert Into @ReturnTable
            Select @myString

    RETURN 
END
GO

And when you are in action try it as below

SQL

select * From SplitString('Hello**John**Smith','**')

Note: The UDF will gonna handle the delimtres whose length is greater than One (>1)...;)
Cheers!