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 |
|
sreyjain
Starting Member
2 Posts |
 Posted - 2009-01-22 : 10:11:27
|
| select substring('Product version # am-021_25 asdf',Charindex('Product version #','Product version # am-021_25 asdf')+12,9) -am-021_25select substring('Product version # 26-012 asdf 255',Charindex('Product version #','Product version # 26-012 asdf 255')+12,6) - 26-012is it possible that i can get the value whatever it is after 'Product version #' till first one blank space. Means on both example there is first blank space after am-021_25 and 26-012 (before 'asdf' and 'asdf 255') means i want like this : select substring('Product version # 26-012 asdf 255',Charindex('Product version #','Product version # 26-012 asdf 255')+12,'') - but i m getting an error - Argument data type varchar is invalid for argument 3 of substring function. as i m showing balnk space - '' so what will be the query if i want the value until first blank space. can anyone tell me please.Thanks. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2009-01-22 : 10:17:07
|
| [code]select left(txt, charindex(' ', txt)-1) as colfrom(select ltrim(replace('Product version # am-021_25 asdf', 'Product version #', '')) as txt) t[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
sreyjain
Starting Member
2 Posts |
Posted - 2009-01-22 : 10:37:21
|
| perfect. Harsh, Thanks a lot!! I appreciate man!!- Srey Jain |
|
|
|
palak
Yak Posting Veteran
55 Posts |
Posted - 2009-01-22 : 14:50:59
|
| i have quite similar requirements but instead of the value i have column - named ordertext from orders tablelike - select left(ltrim(replace(ordertext, 'orderid #', '')), charindex(' ', ltrim(replace(ordertext, 'orderid #', '')))-1) from orders but i m getting an error like - Argument data type text is invalid for argument 1 of replace function.do you have any idea? thanks. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-01-22 : 15:40:08
|
quote:
Originally posted by palak
i have quite similar requirements but instead of the value i have column - named ordertext from orders tablelike - select left(ltrim(replace(ordertext, 'orderid #', '')), charindex(' ', ltrim(replace(ordertext, 'orderid #', '')))-1) from orders but i m getting an error like - Argument data type text is invalid for argument 1 of replace function.do you have any idea? thanks.
Your ordertext column is of the data type text. Replace can only work with character or binary data. |
|
|
|
palak
Yak Posting Veteran
55 Posts |
Posted - 2009-01-22 : 15:56:19
|
| thanks for replying..so in that case, can you tell me what would be my query? as i m also in similar situation just difference is that i have to use column in query instead of value and taht acolumn is text data taype.do anyone have idea?thank you. |
|
|
|
|
|
|
|
|
simple query