Text Field Parsing Question

[mlager]

I've got a type TEXT field where users are putting in values such as:

Field 1: [VALUE1]
Field 2: [VALUE2]
Field 3: [VALUE3]

Is there any way in SQL to take that single TEXT field and create a field for FIELD 1 with the value, FIELD 2 with the value, and FIELD 3 with the value? The value would be enclosed in brackets. There is a potential that the value could cause a CR as well, which is why I put the values in brackets. I.E:

Field 1 Field 2 Field 3
-----------------------------
VALUE1 VALUE2 VALUE3

If necessary, the field names could also be enclosed in brackets, or parenthesis, or any other character. Any help would be great.

 

[r937]

you ~could~ do it with SQL, but it would be horribly complicated and i'd rather have a root canal

why can't you write a quick application language program to do this?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Peager]

This is not elegant but it should give you what you need to develop it further for your own use.

DECLARE @Text VARCHAR(100)
SET @Text = 'Field 1: [VALUE1]
			 Field 2: [VALUE2]
			 Field 3: [VALUE3]'
			 
DECLARE @Fld1 VARCHAR(100)
DECLARE @Fld2 VARCHAR(100)
DECLARE @Fld3 VARCHAR(100)

DECLARE @Start INT
DECLARE @End   INT

SET @Start = CHARINDEX('[',@Text)
SET @End = CHARINDEX(']',@Text)
SET @Fld1 = SUBSTRING(@Text,@Start+1,@End-@Start-1)

SELECT @Start, @End, @Fld1

SET @Start = CHARINDEX('[',@Text,@End)
SET @End = CHARINDEX(']',@Text,@End+1)
SET @Fld2 = SUBSTRING(@Text,@Start+1,@End-@Start-1)
SELECT @Start, @End, @Fld2

SET @Start = CHARINDEX('[',@Text,@End)
SET @End = CHARINDEX(']',@Text,@End+1)
SET @Fld3 = SUBSTRING(@Text,@Start+1,@End-@Start-1)
SELECT @Start, @End, @Fld3

SELECT @Fld1, @Fld2, @Fld3

 

[r937]

nice one, peager

i keep forgetting that T-SQL is actually a powerful superset of SQL

so let me revise what i said...

you ~could~ do it with SQL, but using T-SQL is probably required

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Peager]

Every time I think 'you can't do that in SQL' I pick up one of Itzak Ben-Gan's books and thoroughly humble myself... <VBG>