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.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2005-10-17 : 12:56:57
|
| I need to parse two substrings from two columns in a single select statement if it is possible. The data type for both columns is text.Here is the sample data.Thanks for any help!CREATE TABLE [dbo].[order_header_Test] ( [head_order_nbr] [varchar] (20), [head_ext_notes] [text] , [head_int_notes] [text] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOinsert into order_header_Test values ('111','','10/14/05 >>>emailed proof #1 to ABC@xyz.com')insert into order_header_Test values ('222','10/14/05 ~A this note is for ABC...','')insert into order_header_Test values ('333','','08/14/05 ~A See attached file (filename.txt)')insert into order_header_Test values ('444','~B notes:, ~A notes:','09/14/05 ~A See attached file (aaa.txt)')select head_order_nbr, MyResult1 = case patindex('%~A%', head_ext_notes) when 0 then '' else substring (head_ext_notes,patindex('%~A%', head_ext_notes)+2, 300)end, MyResult2=case patindex('%~A%', head_int_notes) when 0 then '' else substring (head_int_notes,patindex('%~A%', head_int_notes)+2, 300)end from order_header_TestThe above select falls short in several ways.First I want to concatinate MyResult1 and MyRersult2 into one field, but because it is text field, neither + nor & worked.And I failed to add another case statement to wrap around the two MyResults. Anyway, the final result should looks like:For 111, it should be Null or empty string,For 222, it should be 'this note is for ABC...'For 333, it should be 'See attached file(filenam...'For 444, it should be 'notes: See attached file 9aaa.txt)' |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2005-10-18 : 11:59:56
|
| Since my case of case statement got no reply, let me break it down to the next question.How to substring a text field? I need to start with a special character (~A), and end with either the carridge return right after it or the end of the string.I have trouble figuring out the length of the string since len function does not work with text field. Without it, is there another way to find the ending of the string?Here is what I had, and it only worked when there is carridge return.substring (h.head_conds,patindex('%~A%', h.head_conds)+2, patindex('%'+CHAR(13)+CHAR(10)+'%',substring (h.head_conds,patindex('%~A%', h.head_conds)+2, patindex('% %', h.head_conds)))And don't you hate data type text? Does anybody know a good way to check them out and possibly replace them?Thanks! |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
How to nest two Case statements?