| Author |
 Topic  |
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
 Posted - 06/09/2012 : 19:02:42
|
So i have a column which contains data that looks like:
$1000 S/B COATS ***EXPUNGED 022111***
$1000 S/B DORIS PRICE ***EXPUNGED 102109***
CASH $75.00 REC #34271 ***EXPUNGED 032708***
CASH $390.00
What i need to do is pull a substring from those rows that starts at the first character after the '$' and end at the first character before the next ' '(space). Therefore the substring will NOT include the '$' and will NOT include the space. In EVERY row, the value i need falls within that formula (there is always a '$' and there is always a space after the part i need).
Is this is a substring function or is there another way to extract this?
thanks
james |
|
|
tkizer
Almighty SQL Goddess
USA
35020 Posts |
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 06/09/2012 : 19:17:45
|
| i thought so. I forgot about charindex. I'll give it a go and be back when i get my knickers all in a twist. Thanks for jogging my memory. |
 |
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 06/09/2012 : 20:44:45
|
Knickers are officially twisted. Using:
UPDATE douglasconversion.dbo.warrantsjoin SET bondinfo=substring(bondinfo, charindex('$', Bondinfo) + 1, charindex(' ', Bondinfo)-1)
I'm getting the following error:
Msg 537, Level 16, State 4, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.
If i remove the -1 from the end of the query, it executes, but, as you'd expect, it's keeping the space AND the next character over after the space. Undesirable.
help?
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35020 Posts |
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 06/10/2012 : 09:04:41
|
| Ok, i finagled my way through it (i won't tell you how, for fear of being berated), but now my task is to set rows in a column to NULL where there is NO '$' in that row. So, i was thinking that Update Douglasconversion..warrantsjoin set FullBondInfo = NULL where FullBondInfo not like '%$%'., but i suspect that there's a better way to eliminate those rows. |
|
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 06/10/2012 : 09:24:52
|
Ok, the modification i used on the first script was:
UPDATE douglasconversion.dbo.warrantsjoin SET Fullbondinfo=substring(Fullbondinfo, charindex('$', FullBondinfo) + 1, charindex(' ', FullBondinfo)-1) where FullBondInfo is not null
but before that i added an extra space at the end of each row in column to allow the script to run. However, what i'm noticing is that in rows there the value i'm trying to capture has a 'cents' value preceded by a decimal point, the update script is chopping off the two numbers after the decimal point, and keeping only the decimal out and characters to the left of it but to the right of the '$'. I'm trying to figure out how to include something in that substring to capture the cents value, but my caffeine intake hasn't reached critical mass yet, so i'm not getting from point A to point B yet. |
|
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 06/10/2012 : 09:33:12
|
I'm imagining a CASE expression that uses:
UPDATE douglasconversion.dbo.warrantsjoin SET Fullbondinfo=substring(Fullbondinfo, charindex('$', FullBondinfo) + 1, charindex('.[0-9][0-9] ', FullBondinfo)-1) where FullBondInfo is not null
with a second condition in that case that deals with entries with no 'cents' value and no decimal point. |
|
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 06/10/2012 : 10:42:23
|
Also, using:
UPDATE douglasconversion.dbo.warrantsjoin SET Fullbondinfo=substring(Fullbondinfo, charindex('$', FullBondinfo) + 1, charindex('.', FullBondinfo)+2) where FullBondInfo is not null
somehow chops off the trailing zeros in any digit that ends in two zeros. obviously, not what i want.
|
|
|
|
bitsmed
Posting Yak Master
Denmark
107 Posts |
Posted - 06/10/2012 : 16:58:34
|
Parameter # 3 to the substring should be the length of the substring you want, and also you should give "charindex" a starting position (parameter 3) when you calculate the length - so your sql should look something like:
update douglasconversion.dbo.warrantsjoin
set Fullbondinfo=substring(Fullbondinfo,charindex('$',FullBondinfo)+1,charindex(' ',FullBondinfo,charindex('$',FullBondinfo)-charindex('$',FullBondinfo)-2)
where FullBondInfo is not null
|
|
|
|
WJHamel
Aged Yak Warrior
USA
614 Posts |
Posted - 06/11/2012 : 16:55:44
|
| Thanks bitsmed and thanks Tara. Elements of both solutions eventually led me to resolution, but it became far more complicated as i dug deeper in the data, as usual. |
|
|
| |
Topic |
|
Substring
