Seperating Data in a Text String.

[dlingo]

I have a field that looks like this......

116/87 (75%)

I want to seperate this field into 3 fields:

116
87
75%

I know this can be done, but how?

Please advise

 

[tlbroadbent]

Here is one possible solution.

Select left(colname,charindex('/',colname)-1)
From TableName
Union all
Select Substring(colname,charindex('/',colname)+1,charindex('(',colname)-charindex('/',colname)-1)
From TableName
Union all
Select Substring(colname,charindex('(',colname)+1,charindex(')',colname)-charindex('(',colname)-1)
From TableName Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[rac2]

Use string functions CHARINDEX('string', column_name) and
SUBSTRING(column_name, startPosition, length) to break out the pieces.

For example,

SELECT SUBSTRING(examResults, 1, (CHARINDEX('/', examResults) - 1)) FROM myTable

would give you the first piece of data.

See Books Online for a complete list of String Functions.

 

[dlingo]

The above two coding samples seem like they should work, but when I run the code in ASP I keep getting a syntax error. 'Expected CASE'. And it's expecting the CASE between the Select and the Substring, or between the Select and the Left. Any ideas why?

 

[tlbroadbent]

It appears that you are attempting to execute the SQL string on the ASP page (in VB script?). You must create a SQL string and execute it on SQL Server. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[dlingo]

Thank you so much for your help, but I'm still not understanding the ASP set up for the application. I am new to both ASP and SQL. I understand the substring function. I understand the code that was written above, but I think my problem lies in the ASP setup, not in the code. For my application I used a similar setup that was used before I was employed here and that works successfully. Let me show you what I've written so far taking into account the above code and the ASP code written before I was here. Please advise....

SQL_EXPANSION01 = "Select Substring(PacketsStaged,1,(charindex('/',PacketsStaged)-1)) AS PacketsStagedAtt"
SQL_EXPANSION02 = "Select Substring(PacketsStaged,(charindex('/',PacketsStaged)+1),(charindex('(',PacketsStaged)-1) AS PacketsStagedSucc"
SQL_EXPANSION03 = "Select Substring(PacketsStaged,(charindex('(',PacketsStaged)+1),(charindex(')',PacketsStaged)-1) AS PacketsStagedPerc"
set r5 = conn.execute(SQL_EXPANSION01)
set r6 = conn.execute(SQL_EXPANSION02)
set r7 = conn.execute(SQL_EXPANSION03)

set r8 = Server.CreateObject("ADODB.Recordset&quot
r8.Open SQL_EXPANSION01, conn, adOpenStatic
On Error Resume Next
r8.MoveFirst
do while Not r8.eof
loop

set r9 = Server.CreateObject("ADODB.Recordset&quot
r9.Open SQL_EXPANSION02, conn, adOpenStatic
On Error Resume Next
r9.MoveFirst
do while Not r9.eof
loop

set r10 = Server.CreateObject("ADODB.Recordset&quot
r10.Open SQL_EXPANSION03, conn, adOpenStatic
On Error Resume Next
r10.MoveFirst
do while Not r10.eof
loop

The errors that I get are....
Undefined function 'substring' in Expression.
Undefined function 'charindex' in Expression.

 

[rac2]

Is this the complete string that you are creating for the query?

SQL_EXPANSION01 = "Select Substring(PacketsStaged,1,(charindex('/',PacketsStaged)-1)) AS PacketsStagedAtt"

Because if it is then it is not a SQL statement so naturally you will get some kind of error message. You need to add a FROM clause that will specify the table that has a column named PacketsStaged.

Unless there is something here I don't know about, which is more than possible.

 

[dlingo]

On the load of the ASP page I reference the proper database. Therefore, I don't need the from command, or at least I haven't to this point in the project. It has to do with the actual functions. Seems like they are not recognized.

 

[Spellman]

Could you find out at exactly which statement the code breaks down for me. I think the best way is just to stick in a couple of message boxes into your code.