Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 Stored procedure to separate a word

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.
Go to Top of Page

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 Avinash

like 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.
Go to Top of Page

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 TheRest
from @Sample


/* Results
FirstBit TheRest
------------------------------ ------------------------------
Hi, how are you doing.
Hello Ryan
Hello Avinash
*/


Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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 + ';%'

/* Results
s s
------------------------------ ----------------------------------------------------------------------------------------------------
Hi, how are you doing. Hi, how are you doing.;Hello Ryan;Hello Avinash
Hello Ryan Hi, how are you doing.;Hello Ryan;Hello Avinash
Hello Avinash Hi, how are you doing.;Hello Ryan;Hello Avinash
Something 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.
Go to Top of Page

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=54333

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -