wellsuited
asked on
Split Access Field
i have a field called Field1 the value of field1 is
41115455445151&2/16/2009&1 23&demo
I need to seperate this field at the &
Results would be
Newfield1 = 41115455445151
Newfield2 = 2/16/2009
NewField3 = 123
NewField4 = demo
41115455445151&2/16/2009&1
I need to seperate this field at the &
Results would be
Newfield1 = 41115455445151
Newfield2 = 2/16/2009
NewField3 = 123
NewField4 = demo
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, I forgot to tell: My solution works in MS SQL Server :-)
LEFT(Field1, CHARINDEX('&',Field1)-1) AS Newfield1,
SUBSTRING(Field1, CHARINDEX('&',Field1)+1,
CHARINDEX('&',Field1,CHARI
RIGHT(RTRIM(Field1), CHARINDEX('&',REVERSE(RTRI
FROM YourTable
Of course, the NewField2 is of char type, to convert it to date you have to use CONVERT function.