Substring - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Substring

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-06-09 : 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

38200 Posts

Posted - 2012-06-09 : 19:16:26

I'm not in front of SQL Server right now, but I'd use CHARINDEX twice to find both locations (add one and subtract one) and then SUBSTRING.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-06-09 : 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

651 Posts

Posted - 2012-06-09 : 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

38200 Posts

Posted - 2012-06-09 : 21:27:02

It's because you have data that doesn't contain the space, like that last row in your example.

What does just charindex(' ', Bondinfo)-1) return for that last row? I can't remember if you'll get a zero or a null here. If it's a null, you can use COALESCE around it and add in DATALENGTH parameter for Bondinfo.

You could also use a CASE statement to handle the condition.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-06-10 : 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

651 Posts

Posted - 2012-06-10 : 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

651 Posts

Posted - 2012-06-10 : 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

651 Posts

Posted - 2012-06-10 : 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
Aged Yak Warrior

545 Posts

Posted - 2012-06-10 : 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

651 Posts

Posted - 2012-06-11 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources