Parse Varchar Field

Question

Post reply

Parse Varchar Field

texpic

SSCertifiable

Points: 5887
More actions
February 13, 2006 at 9:38 am

#96596

I'm working on a program for my kid's school. I just figured out how get the questions they answered incorrect into a SQL database. There are two fields. Name and IncorrectQuestions.
This is what the incorrect data looks like (sample of three records):
1 + 5 2 + 2 4 + 1 3 + 3 1 + 1
4 + 1 3 + 3
1 / 1 100 / 10 50 * 3
I need to parse it and create a table with two fields, KidsName and Incorrect (so the data will look like this now):
1 + 5
2 + 2
4 + 1
3 + 3
1 + 1
4 + 1
3 + 3
1 / 1
100 / 10
50 * 3
I'm kind of brain dead on how to do this so any help would be appreciated. Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Liliana Sorrentino SSC-Addicted Points: 400 More actions · Answer 1

Hi,

Take a copy of your table before:

CREATE TABLE #test (name varchar(40), iq varchar (100))

INSERT #test SELECT 'Juan', '1 + 5 2 + 2 4 + 1 3 + 3 1 + 1'

INSERT #test SELECT 'Luis', '4 + 1 3 + 3'

INSERT #test SELECT 'Raul', '1 / 1 100 / 10 50 * 3'

WHILE (SELECT MAX(LEN(iq)) FROM #test) > 0

BEGIN

SELECT NAME,

CASE WHEN PATINDEX('% %', iq) > 0

THEN SUBSTRING(IQ, 1, PATINDEX('% %', iq) -1)

ELSE IQ END

FROM #test

WHERE LEN(iq) > 0

UPDATE #test

SET iq = CASE WHEN PATINDEX('% %', iq) > 0

THEN SUBSTRING(iq, PATINDEX('% %', iq) + 4, LEN(IQ))

ELSE '' END

WHERE LEN(iq) > 0

END

Hope this help,

Liliana.

texpic SSCertifiable Points: 5887 More actions · Answer 2

Thanks I'm on my way to visit the school now but will try this when I return.

texpic SSCertifiable Points: 5887 More actions · Answer 3

That works great. I'm embarrassed to ask this, but how can I get it into a table, LOL.

Lets assume I want the results I see on the screen added to a table called #TempDone with two fields, UserName and IncorrectQuestions

Thanks again!

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 4

Define #tempDone like Liliana defined #test, then slightly modify her code:

insert into #TempDone SELECT NAME,

CASE WHEN PATINDEX('% %', iq) > 0

THEN SUBSTRING(IQ, 1, PATINDEX('% %', iq) -1)

ELSE IQ END

FROM #test

WHERE LEN(iq) > 0

A set-based method (without the loop and without changing table #test) can also be constructed. Let me know if you want it...

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 5

Create a table with one column

and then use this query :-

insert into tbl

select replace(name,' ',char(13))

ok bye!!!!!

texpic SSCertifiable Points: 5887 More actions · Answer 6

I'm a little slow at SQL, but usually can "get it" after looking at it a while. I'm having a hard time getting Liliana's code into a table.

Jesper I wasn't sure how to drop your modification into her code.

Thanks again, these loop things confuse me.

Liliana Sorrentino SSC-Addicted Points: 400 More actions · Answer 7

Hi,

This is the change to insert data in a new table, but it would be very interesting to have Jesper's suggest, without while or change table #test:

CREATE TABLE #test (name varchar(40), iq varchar (100))

INSERT #test SELECT 'Juan', '1 + 5 2 + 2 4 + 1 3 + 3 1 + 1'

INSERT #test SELECT 'Luis', '4 + 1 3 + 3'

INSERT #test SELECT 'Raul', '1 / 1 100 / 10 50 * 3'

CREATE TABLE #TempDone (name varchar(40), rq varchar (100))

WHILE (SELECT MAX(LEN(iq)) FROM #test) > 0

BEGIN

INSERT #TempDone

SELECT NAME,

CASE WHEN PATINDEX('% %', iq) > 0

THEN SUBSTRING(IQ, 1, PATINDEX('% %', iq) -1)

ELSE IQ END

FROM #test

WHERE LEN(iq) > 0

UPDATE #test

SET iq = CASE WHEN PATINDEX('% %', iq) > 0

THEN SUBSTRING(iq, PATINDEX('% %', iq) + 4, LEN(IQ))

ELSE '' END

WHERE LEN(iq) > 0

END

SELECT * FROM #Test

SELECT * FROM #TempDone

Liliana.

JeffB Hall of Fame Points: 3541 More actions · Answer 8

This won't create a table with 1 row for each incorrect answer. The output may look like what is desired but this is only a function of Query Analyzer.

texpic SSCertifiable Points: 5887 More actions · Answer 9

works perfect Liliana ... thanks again for everything

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 10

OK, then I will write down what I had in mind

It's a well-known trick... It's not as readable as your straightforward solution. On the other hand, it's pretty fast, I think.

First you need to create a Numbers table - a table that is often useful when you are writing set-based queries:

SELECT TOP 10000 n = IDENTITY(INT, 1, 1) INTO Numbers

FROM

sysobjects a1

CROSS JOIN

sysobjects a2

CROSS JOIN

sysobjects a3

ALTER TABLE Numbers

ADD CONSTRAINT Index_Numbers PRIMARY KEY CLUSTERED(n)

GO

The code is as follows:

CREATE TABLE #test (name varchar(40), iq varchar (100))

INSERT #test SELECT 'Juan', '1 + 5 2 + 2 4 + 1 3 + 3 1 + 1'

INSERT #test SELECT 'Luis', '4 + 1 3 + 3'

INSERT #test SELECT 'Raul', '1 / 1 100 / 10 50 * 3'

select t.Name, substring(t.iq + ' ', N.n, charindex(' ', t.iq + ' ', N.n) - N.n)

from #test t cross join Numbers N

where

substring(t.iq, N.n - 4, 4) = ' ' or N.n = 1

order by t.Name, N.n

drop table #test

Liliana Sorrentino SSC-Addicted Points: 400 More actions · Answer 11

Jesper,

Thank you very much for the code, it's great!

Liliana.

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 12

As I said, it's a well-known trick - I have learned it here at SSC...