text field with a number of values

[nathanharcup]

Hi all,
I have a column nvarchar(max) that contains any number of records as below;

record1

record2

record3

each of these values originates from another table in the db, does anyone have any code that i could use to split the values and work on them independantly?

Thanks

 

[bborissov]

If all records has the same pattern:
"RECORD"+Any number, you could use REPLACE() function:

SELECT REPLACE(YourField,'record','') AS SomeNumbers
FROM YourTable

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[nathanharcup]

Unfortunatly the values do not follow a patern.

I esentially want to take each value put it into a variable and then run a select & insert statement to lookup each value in it's source table and insert it into another

 

[hmckillop]

I think all you need is a split function

Have a look at this and pick the function which best suits your need

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Moptop]

A slightly funky way to do this is to format the text as XML and then to process it as a recordset.
Eg. Say that the text contains Record1|Record2|Record3
Then use the Replace function and a bit of string handling to make it
<data><record>Record1</record><record>Record2</record><record>Record3</record></data>
Then you could use the select * from openxml on the string

 

[hmckillop]

Moptop,

I dont think thats probably practical in this case. Whilst XML handling is much improved in later versions of SQL, the above problem states the data is already stored in a non-XML format. To spend the time to format it as XML would be a labourous process and impracticable.

As with alot of problems, I guess people are dealing with badly designed DBs or poorly implemented DBs.
Its us plebs who are left to deal with the consequences.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[simian101]

Perhaps you could post an example.

Simi