select if from string and insert

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

SQL Server 2008 Forums

Transact-SQL (2008)

select if from string and insert

Author

Topic

gisdataguy
Starting Member

2 Posts

Posted - 2011-08-11 : 14:50:32

I have a table named dbo.MRKAR with a column that is a string named [equation] and it is basically a long string of complex math that is coming from a commerical application. In this column, about half of the rows have this in the string: blah blah xyz B/0.87402 xyx blah blah... It is a B coefficient.

What i need some direction on is how to write the tsql so that:

If the column contains a B/
take the number immediately after the / and before the " " <--- space.

then take the number and insert it into another table named dbo.production with a column named [Bcoef]

If there is no B/ then enter EXP in the column of [Bcoef] in table dbo.production

Thank you!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-11 : 15:29:00

This isolates the number part, though not very effeciently
DECLARE @str varchar(100)
SET @str = 'blah blah xyz B/0.87402 xyx blah blah'

SELECT CASE WHEN PATINDEX('%B/%',@str) > 1
THEN SUBSTRING(@str,PATINDEX('%B/%',@str)+2,charindex(' ',substring(@str,PATINDEX('%B/%',@str),50))-2 )
ELSE 'EXP'
END

Jim

Everyday I learn something that somebody else already knew

gisdataguy
Starting Member

2 Posts

Posted - 2011-08-12 : 10:22:16

Does this make sense...??

insert into [dbo].[Production_Test] ([PropNum],[Bcoef])
select PropNum
--,Equation
--,CHARINDEX('B/',Equation,0) as 'B location'
--,CHARINDEX('B/',Equation,0)+2 as 'Start of Coef'
--,Case when CHARINDEX(' ',Equation,CHARINDEX('B/ ',Equation,0)+3) <= 0 then LEN(Equation) else CHARINDEX(' ',Equation,CHARINDEX('B/ ',Equation,0)+3) end as 'space location after the B/ '
--,(Case when CHARINDEX(' ',Equation,CHARINDEX('B/ ',Equation,0)+3) <= 0 then LEN(Equation) else CHARINDEX(' ',Equation,CHARINDEX('B/ ',Equation,0)+3) end) - (CHARINDEX('B/',Equation,0)+2 ) as CharLength
,
Case when CHARINDEX('B/',Equation,0) = 0 then
'EXP'
else
ltrim(rtrim(substring(Equation,CHARINDEX('B/',Equation,0)+2,(Case when CHARINDEX(' ',Equation,CHARINDEX('B/ ',Equation,0)+3) <= 0 then LEN(Equation) else CHARINDEX(' ',Equation,CHARINDEX('B/ ',Equation,0)+3) end) - (CHARINDEX('B/',Equation,0)+1 ))))
end as Bcoef
from [dbo].[MRKAR]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources