Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
I have data in a column that i need to parse out and populate other columns with.The origninating column is a nvarchar(255). That column is stuffed with a bunch of data with each character position representing something different. BAD design. NOT my doing.Sample data:120302D EH 60478.15 Y120302D EP 286.70 Y120302D ED 334.25 YYIn every case:Character 1 through character six represents a date. I have a script to grab that.Character 7 represents a varchar(1) value. I have a script to grab that.Character 8 is always a spaceCharacter 9 represensts a varchar(1) value. I have a script to grab thatCharacter 10 represents a varchar(1) value. I have a script to grab that.after that i may have one, two, or three spaces before i have a money value. This is where i need a substring using a charindex to grab that value, but i'm not sure how to code it. After the money value i then have values i need to grab which are preceded by either one two or three spaces, but are always adjacent to each other without a space. There may or may not be a value in the last position, but there is always a value in the second to last position.So it's the Money values i need help with as well as the values that follow.How would that substring / charindex (patindex) script look?
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts
Posted - 2012-11-19 : 10:05:49
Here is an example. Replace @x with your column name. Also, change the [^0-9.-] with the characters you want to allow in the numeric part.