SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Substring select		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WJHamel
Aged Yak Warrior

USA
614 Posts
Posted - 11/19/2012 :  09:51:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/19/2012 :  10:05:49  Show Profile  Reply with Quote
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

USA
614 Posts
Posted - 11/19/2012 :  10:26:39  Show Profile  Reply with Quote
Thank you, as always.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000