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 select

Author  Topic 

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-11-19 : 09:51:50
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 Y
120302D EP 286.70 Y
120302D ED 334.25 YY

In 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 space

Character 9 represensts a varchar(1) value. I have a script to grab that

Character 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.
DECLARE @x VARCHAR(32) = '120302D ED 334.25 YY'
SELECT SUBSTRING(LTRIM(STUFF(@x,1,10,'')),1,PATINDEX('%[^0-9.-]%', LTRIM(STUFF(@x,1,10,''))+' '))
Go to Top of Page

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-11-19 : 10:26:39
Thank you, as always.
Go to Top of Page
   

- Advertisement -