Pattern Search Using Sql 2005

[sleezy]

Create Table #test (string Varchar(100))

Insert #test Values ('80 98439 9991 997 -50.000' )
Insert #test Values ('0997 2247' )
Insert #test Values ('80 98416 64432 997 -2.250' )
Insert #test Values ('80 98402 64432 997 -0.320' )
Insert #test Values ('80 98416 64432 997 -2.250' )
Insert #test Values ('80 98402 64432 997 -0.320' )
Insert #test Values ('01 6060 3 997 -1000.000 Cb')
Insert #test Values ('ab Bosman Street 3 Toria' )
Insert #test Values ('80 98401 3 997 -10.000' )
Insert #test Values ('80 98418 3 997 -5.000' )

How Do I Select Just The Monetary Values In This String ?

[rmiao]

In sql server, you can find position of '-' with patindex or charindex function then take sub-string from there.

[MAK]

use tempdb
--Step 0 Simualte table and data
Create Table #test (string Varchar(100))

Insert #test Values ('80 98439 9991 997 -50.000' )
Insert #test Values ('0997 2247' )
Insert #test Values ('80 98416 64432 997 -2.250' )
Insert #test Values ('80 98402 64432 997 -0.320' )
Insert #test Values ('80 98416 64432 997 -2.250' )
Insert #test Values ('80 98402 64432 997 -0.320' )
Insert #test Values ('01 6060 3 997 -1000.000 Cb')
Insert #test Values ('ab Bosman Street 3 Toria' )
Insert #test Values ('80 98401 3 997 -10.000' )
Insert #test Values ('80 98418 3 997 -5.000' )

--Step1 - Find Hyphen
select charindex('-',string) from #test

--Step2 Display all data after hypen
select substring(string,charindex('-',string)+1,len(string)-charindex('-',string)) from #test

--Step 3 Ignore all the non numeric values
select
case when charindex('-',string) =0 then NULL else
substring(string,charindex('-',string)+1,len(string)-charindex('-',string))
end
from #test

[sleezy]

It works but only picks ups amounts where there is a '-' sign some amount have no minus and this returns a null

ie. Data sample

80 98416 64432 997 22.250
80 98402 64432 997 100.320
80 98416 64432 993 2.250
80 98416 64432 991 2.250

I tried the script below but it returns the whole record if the amount is positive


SELECT
RowId
,REPLACE(SUBSTRING(LTRIM(STRING),CHARINDEX(' -',STRING) + 1,LEN(STRING)),'CB','') AS AMOUNT
,STRING
FROM #TEST

use tempdb
--Step 0 Simualte table and data
Create Table #test (string Varchar(100))

Insert #test Values ('80 98439 9991 997 -50.000' )
Insert #test Values ('0997 2247' )
Insert #test Values ('80 98416 64432 997 -2.250' )
Insert #test Values ('80 98402 64432 997 -0.320' )
Insert #test Values ('80 98416 64432 997 -2.250' )
Insert #test Values ('80 98402 64432 997 -0.320' )
Insert #test Values ('01 6060 3 997 -1000.000 Cb')
Insert #test Values ('ab Bosman Street 3 Toria' )
Insert #test Values ('80 98401 3 997 -10.000' )
Insert #test Values ('80 98418 3 997 -5.000' )

--Step1 - Find Hyphen
select charindex('-',string) from #test

--Step2 Display all data after hypen
select substring(string,charindex('-',string)+1,len(string)-charindex('-',string)) from #test

--Step 3 Ignore all the non numeric values
select
case when charindex('-',string) =0 then NULL else
substring(string,charindex('-',string)+1,len(string)-charindex('-',string))
end
from #test

[rmiao]

What you really need to do is normalize the table.

[MAK]

Here we go
use tempdb
--Step 0 Simualte table and data
--drop table #test

Create Table #test (string Varchar(100))

Insert #test Values ('80 98439 9991 997 -50.000' )
Insert #test Values ('0997 2247' )
Insert #test Values ('80 98416 64432 997 -2.250' )
Insert #test Values ('80 98402 64432 997 -0.320' )
Insert #test Values ('80 98416 64432 997 -2.250' )
Insert #test Values ('80 98402 64432 997 -0.320' )
Insert #test Values ('01 6060 3 997 -1000.000 Cb')
Insert #test Values ('ab Bosman Street 3 Toria' )
Insert #test Values ('80 98401 3 997 -10.000' )
Insert #test Values ('80 98418 3 997 -5.000' )
Insert #test Values ('80 98416 64432 997 22.250' )
Insert #test Values ('80 98402 64432 997 100.320' )
Insert #test Values ('80 98416 64432 993 2.250' )
Insert #test Values ('80 98416 64432 991 2.250' )

--Step 1 - Find decimal
select charindex('.',string) from #test

--Step2 - Find characters after decimal
select substring(string,charindex('.',string),len(string)-charindex('.',string)+1) from #test

--Step3 - Ignore the non decimal from step 2
select case when charindex('.',string) =0 then NULL else
substring(string,charindex('.',string),len(string)-charindex('.',string)+1)
end
from #test

--Step4 - Find characters to the left of decimal
select reverse(substring(reverse(string), charindex('.',reverse(string))+1,
charindex(' ',reverse(string),charindex('.',reverse(string))) - charindex('.',reverse(string))))
from #test

--Step5 - Ignore non decimals from step 4

select case when charindex('.',reverse(string)) = 0 then NULL else
reverse(substring(reverse(string), charindex('.',reverse(string))+1,
charindex(' ',reverse(string),charindex('.',reverse(string))) - charindex('.',reverse(string))))
end from #test

--Step 6 Merge step 5 and step 3

select case when charindex('.',reverse(string)) = 0 then NULL else
reverse(substring(reverse(string), charindex('.',reverse(string))+1,
charindex(' ',reverse(string),charindex('.',reverse(string))) - charindex('.',reverse(string)))) end+
case when charindex('.',string) =0 then NULL else
substring(string,charindex('.',string),len(string)-charindex('.',string)+1)
end
from #test

[MAK]

--Query
select String as ActualText,MonetoryValue= case when charindex('.',reverse(string)) = 0 then NULL else
reverse(substring(reverse(string), charindex('.',reverse(string))+1,
charindex(' ',reverse(string),charindex('.',reverse(string))) - charindex('.',reverse(string)))) end+
case when charindex('.',string) =0 then NULL else
substring(string,charindex('.',string),len(string)-charindex('.',string)+1)
end
from #test

--Results
ActualText,MonetoryValue
80 98439 9991 997 -50.000, -50.000
0997 2247,NULL
80 98416 64432 997 -2.250, -2.250
80 98402 64432 997 -0.320, -0.320
80 98416 64432 997 -2.250, -2.250
80 98402 64432 997 -0.320, -0.320
01 6060 3 997 -1000.000 Cb, -1000.000 Cb
ab Bosman Street 3 Toria,NULL
80 98401 3 997 -10.000, -10.000
80 98418 3 997 -5.000, -5.000
80 98416 64432 997 22.250, 22.250
80 98402 64432 997 100.320, 100.320
80 98416 64432 993 2.250, 2.250
80 98416 64432 991 2.250, 2.250