sql split text after [

[arne01]

Hi,

Within a SQL tabel I have a text field filled as follows:

Name [Color]

Example

polo shirt [Ocean Blue]

I would like to split this field in 2 parts:

1 Name
2 Color

Or as in example

1. polo shirt
2. Ocean Blue

Can someonec give me any direction?

[Scally_Ally]

sql has a few built in functions [w3schools.com...] but nothing to handle what you are looking for.

I think what you will need to do is pull the information out of the database then process it before displaying it on your page.

Something like pull info from db, split the relevant string into an array using the split() function and then display the information needed on the page

[mattglet]

Best case scenario would be to have the Name and Color be two separate fields.

The SQL functions you should pay attention to are CHARINDEX(), SUBSTRING(), and LEN(). Using a combination of those will get you what you need.

[Scally_Ally]

something like this should do it..

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("mydb.mdb"))
set rs=Server.CreateObject("ADODB.recordset")
sql = "SELECT * FROM table ORDER BY ID DESC"
rs.Open sql, conn
do until rs.eof

myField = rs.fields("myField")
myField = Split(myField, "[")
myField(1) = Replace(myField(1),"]","")

response.write "1. " & myField(0) & "<br>"
response.write "2. " & myField(1) & "<br>"

rs.movenext
loop
rs.close
conn.close

I havent checked it but it should be ok...... i think...