| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2010-01-24 : 07:21:51
|
| i have a nvarchar field that holds a long string (100 characters)i'd like to select and parse it so I can update another field with just from after it starts Reference: until it says LocalDateTime: so I only get what's between that text how can I do that in the easiest way? |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-01-24 : 09:01:33
|
Dear esthera,more than 1000 posts and still not able to give examples so we can help without asking many questions to make it more clear?so I only get what's between that text between what?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-24 : 09:11:53
|
quote:
Originally posted by esthera
i have a nvarchar field that holds a long string (100 characters)i'd like to select and parse it so I can update another field with just from after it starts Reference: until it says LocalDateTime: so I only get what's between that text how can I do that in the easiest way?
seems like you need to use substring along with patindex functions so as get the indexes of start positions of patterns. To get more help you could post exact scenario with sample data |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-01-24 : 09:25:32
|
| sample data is table transaction fulltextdata="xxx: ddfdf Reference: 45345245435 LocalDateTime:10/01/2010"I want to putll out just the 45345245435 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-24 : 09:27:23
|
quote:
Originally posted by esthera
sample data is table transaction fulltextdata="xxx: ddfdf Reference: 45345245435 LocalDateTime:10/01/2010"I want to putll out just the 45345245435
need to know if this will be consistent for all rows i.e can there be rows without any one part? |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-01-24 : 09:38:47
|
| yes this will be consistent though sometimes the fulltextdata is null and sometimes there might be nothing between reference: and localdatetime |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-24 : 09:43:04
|
quote:
Originally posted by esthera
yes this will be consistent though sometimes the fulltextdata is null and sometimes there might be nothing between reference: and localdatetime
does that mean the fulltextdata bit will be not present? |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-01-24 : 13:33:08
|
| no either it's null or the data is there but just a space in between |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-24 : 13:35:11
|
quote:
Originally posted by esthera
no either it's null or the data is there but just a space in between
and when its not null it always has reference and localdatatime? |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-01-24 : 13:41:18
|
| yes |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-25 : 02:16:15
|
quote:
Originally posted by esthera
sample data is table transaction fulltextdata="xxx: ddfdf Reference: 45345245435 LocalDateTime:10/01/2010"I want to putll out just the 45345245435
Where do you want to use extracted data?MadhivananFailing to plan is Planning to fail |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-01-25 : 02:35:23
|
| I want to update a new field in the table with it. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-25 : 02:39:16
|
quote:
Originally posted by esthera
I want to update a new field in the table with it.
What if new data are added periodically?MadhivananFailing to plan is Planning to fail |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2010-01-25 : 02:49:30
|
| i want to run a one time update now |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-25 : 03:02:42
|
quote:
Originally posted by esthera
i want to run a one time update now
See if you can apply thisdeclare @string varchar(1000)set @string='fulltextdata="xxx: ddfdf Reference: 45345245435 LocalDateTime:10/01/2010"'select substring(data,1,charindex(' ',data)-1) from(select substring(@string,charindex('Reference: ',@string)+11,len(@string)) as data) as tMadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-27 : 03:02:53
|
quote:
Originally posted by esthera
i want to run a one time update now
Did my solution work?MadhivananFailing to plan is Planning to fail |
|
|
|
|
parsing string