split a string and insert into table

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,

In my scenario i have a title column and i want to select each word in title and insert seperately into another table.
For example:
Table name is Book
Id Tilte
1 One day in my life

second table name is Master
id title
1 one
2 day
3 in
4 my
5 life
like these i want to insert into master table.

Posted 8-Jan-13 21:47pm

josh-jw

Add a Solution

1 solution

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)

OriginalGriff · Accepted Answer · 2013-01-08T21:59:00

Solution 1

You will probably want this as a stored procedure with @INSTR as a parameter, but it should work:

SQL

DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = 'One day in my life '
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ' '
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO MyTable (Title) VALUES (@VALUE)
END

Posted 8-Jan-13 21:59pm

OriginalGriff

Comments

josh-jw 9-Jan-13 4:27am

Thanks,Griff.
Is it possible that my table containing 10 tiltes.within one select i can pass all titles into this approach and do the above operation?

OriginalGriff 9-Jan-13 4:32am

Sorry? I don't understand what you are asking.
Would you mind trying to ask that in a different way, perhaps with an example?

josh-jw 9-Jan-13 5:45am

i would like to say another scenario.
is it possible that i call one stored procedure inside a function and after that i call that function in query like
select title,fn(title) from book.?

OriginalGriff 9-Jan-13 5:54am

Why would you want to do that? Every time you tried to retrieve info on the title "One day in my life" is would add 5 rows to a table, which already should contain them.
I would create an SP which did both inserts: the main title, and each word and use that instead of trying to insert the title.
What are you trying to achieve with this anyway? There may be a better way to do it.

josh-jw 9-Jan-13 5:58am

i want to achieve the scenario like my book table containing 10 title and i want to insert that title like each word as one record into master table.i want to do
selecting and inserting at a time,like bulk insertion.

OriginalGriff 9-Jan-13 6:05am

I understand that, but why? What does it give you? How does it help you? What are you trying to do with the individual words?

josh-jw 9-Jan-13 6:08am

actually current task is that one .i know that will not help.but i have to do like that only. if do the scenario with i explained above i am getting error like
"Only functions and extended stored procedures can be executed from within a function."

josh-jw 9-Jan-13 6:08am

is there any other way to do this scenario?

OriginalGriff 9-Jan-13 6:11am

Strange thing to do, but...
Just make it a function instead of a stored procedure.
http://msdn.microsoft.com/en-us/library/ms186755(v=sql.105).aspx

josh-jw 9-Jan-13 6:26am

BUt we cant do insertion inside function.

OriginalGriff 9-Jan-13 6:38am

Oops! I forgot that! You can't use a function then...
You wiull have to try an extended stroed procedure - but I have never needed one, so you are venturing into uncharted waters for me!
http://support.microsoft.com/kb/190987
might help.

josh-jw 9-Jan-13 6:48am

didnt get any idea .i think i cant do bulk insertion.loop concept have to implement here.

OriginalGriff 9-Jan-13 7:18am

Why are you trying to do it anyway? Why store each word in a separate row?

josh-jw 9-Jan-13 7:30am

for filtering in user interface. how we can use while loop in this scenario? is it possible to select one row from result set and send title to stored procedure in while loop?

OriginalGriff 9-Jan-13 7:43am

You mean as in "find me all the titles that contain the word 'day'"?
Have you tried
SELECT * FROM MyTable WHERE Title LIKE '%day%'