|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-25 : 08:16:07
|
| Mike writes "I have a string, >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~]and I would like to extract parts of it and send it to a SQL database. here is the layout and data that should be in each column. The Data column will be filled in automatically, but once that happens I would like the other columns to get their data from the Data column.Data = >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~]Display_Address = 99CE = 4.12TPK = 2251Si= 1.63C = 3.58TPL = 2150TPS = 2050Can anyone tell me how to do this?" |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-08-25 : 08:27:12
|
[code]Declare @testVal varchar(100)Set @testVal = '>!~[99DC# 4.12$2251.% 1.63& 3.58''2150.(-2050.>/~]'Select dbo.getCharacters(testVal,'0-9.'), dbo.getCharacters(substring(testVal,0,b1),'0-9.'), dbo.getCharacters(substring(testVal,b1,b2-b1),'0-9.'), dbo.getCharacters(substring(testVal,b2,b3-b2),'0-9.'), dbo.getCharacters(substring(testVal,b3,b4-b3),'0-9.'), dbo.getCharacters(substring(testVal,b4,b5-b4),'0-9.'), dbo.getCharacters(substring(testVal,b5,b6-b5),'0-9.')From ( Select testVal = @testVal, b1 = charindex('#',@testVal), b2 = charindex('$',@testVal), b3 = charindex('%',@testVal), b4 = charindex('&',@testVal), b5 = charindex('''',@testVal), b6 = charindex('(',@testVal) ) blah[/code]Using the following function:[code]CREATE FUNCTION dbo.GetCharacters(@myString varchar(500), @validChars varchar(100))RETURNS varchar(500) ASBEGIN While @myString like '%[^' + @validChars + ']%' Select @myString = replace(@myString,substring(@myString,patindex('%[^' + @validChars + ']%',@myString),1),'') Return @myStringEND[/code]Corey Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."  |
 |
|
SQL noob needs help