| Author |
Topic |
|
Avinash N
Starting Member
6 Posts |
 Posted - 2008-04-16 : 09:55:24
|
| Hi,I am new to sql...can anyone help me by giving the code to write a stored procedure to seperate a word.For example:If there is a string - Hi, how are you doing.I want to seperate "Hi," and "How are you doing." using a stored procedure.Thanks,Avi. |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-04-16 : 10:16:46
|
| What rules determine how you do the splitting?Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
Avinash N
Starting Member
6 Posts |
Posted - 2008-04-17 : 00:55:01
|
| Hi Ryan,Yes thats right..ok.. here goes the problem.Consider a source strings and a target strings with their ItemId, SourceId, TargetId and other attributes.Now you migrated the source contents - that is source strings to target strings. During migration, each source string is appended/concatenated with a constant text (For example say - 'Hello')so, if the source string1 = Ryan then Target string1 = Hello Ryan source string2 = Avinash then Target string2 = Hello Avinashlike this the migration goes.Now, I have to compare the source string with the target string in the database.For this I have to remove or seperate the concatenated text 'Hello'.How to do this..??Thanks,Avi. |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-04-17 : 04:47:52
|
How’s this?--Preparation (you may not need some of this if you have it already)declare @Sample table (TargetString varchar(30))insert @Sample select 'Hi, how are you doing.'union all select 'Hello Ryan'union all select 'Hello Avinash'--Query (the bit you need)select substring(TargetString, 0, charindex(' ', TargetString)) as FirstBit, substring(TargetString, charindex(' ', TargetString) + 1, 100) as TheRestfrom @Sample/* ResultsFirstBit TheRest------------------------------ ------------------------------Hi, how are you doing.Hello RyanHello Avinash*/Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
Avinash N
Starting Member
6 Posts |
Posted - 2008-04-17 : 09:03:03
|
| Hi Ryan,Thank u very much.....The query worked very well... another issue here... 2 tables... source and target... data from source goes into target table under same field... ISSUE IS... data from 'n' no. of records from source table goes into a single record in the target table with delimiter being a semi colon (;)... (n >=2)... how do we compare the data under this particular field now... do we use a if then loop for identifying when the ; ends in target data... kindly help...Thanks,Avi. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2008-04-17 : 09:21:51
|
| Do a search on the forum for some string functions by a guy named Igor. He was very smart.Damian"A foolish consistency is the hobgoblin of little minds." - Emerson |
|
|
|
Avinash N
Starting Member
6 Posts |
Posted - 2008-04-17 : 23:57:15
|
Hi Merkin,i could not find the string functions link by igor.could u help me by giving the link if you have. Thanks,Avi. |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-04-18 : 04:50:39
|
quote:
Originally posted by Avinash N
Hi Ryan,Thank u very much.....The query worked very well... another issue here... 2 tables... source and target... data from source goes into target table under same field... ISSUE IS... data from 'n' no. of records from source table goes into a single record in the target table with delimiter being a semi colon (;)... (n >=2)... how do we compare the data under this particular field now... do we use a if then loop for identifying when the ; ends in target data... kindly help...Thanks,Avi.
How’s this? --Preparation (you may not need some of this if you have it already)declare @Source table (s varchar(30))insert @Source select 'Hi, how are you doing.'union all select 'Hello Ryan'union all select 'Hello Avinash'union all select 'Something else'declare @Target table (s varchar(100))insert @Target select 'Hi, how are you doing.;Hello Ryan;Hello Avinash'--Query (the bit you need)select * from @Source a left outer join @Target b on ';' + b.s + ';' like '%;' + a.s + ';%' /* Resultss s------------------------------ ----------------------------------------------------------------------------------------------------Hi, how are you doing. Hi, how are you doing.;Hello Ryan;Hello AvinashHello Ryan Hi, how are you doing.;Hello Ryan;Hello AvinashHello Avinash Hi, how are you doing.;Hello Ryan;Hello AvinashSomething else NULL*/ If you don't think that will help, post your table structure and an example...Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-04-18 : 04:54:13
|
| By the way, I think Merkin was referring to this link, although I'm not sure it helps the specific problems you've been describing in this thread. It might be worth taking a look at though.http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=54333Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
Avinash N
Starting Member
6 Posts |
Posted - 2008-04-21 : 05:27:24
|
| hey ryan... guess wht i wrote was a bit confusing... wht i meant was tht there are 2 tables source and target... and data from n no. of records in source table goes into target table under the same field (n>=2).. example... source -- hello ... how are you... am gud... (3 records). target -- hello;how are you;am gud (single record, delimiter is semi colon ; ) ...now how do we compare the 3 records of source table to the single record in target table... since n is variable i guess we need a if then loop to compare (cause no. of ; cannot be determined in the target table record)...Thanks,Avi. |
|
|
|
|
Stored procedure to separate a word