identifying a partern - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

identifying a partern

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-02 : 12:50:32

I've data as below in the intermediate table, I want to have the data in final table as below

'a','b','c','d'
'a','b','c','x,y,z'

The table should have something like


c1  c2  c3  c4
--------------------
a   b   c   d
a   b   c   x
a   b   c   y
a   b   c   z

I have written code using charindex & String function to handle for first column data as below and it is working perfectly however now I've data as above.


'a','b,c,d'
c1 c2
a  b
a  c
a  d

-Neil

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-02 : 12:54:07

Should be very similar to the code you have.
The identifier is the first 3 columns instead of just the first and yoou parse the 4th instead of the 2nd. (If I understand what you are trying to do.)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-02 : 13:09:07

need to develope code to identify

'a','b','c','d'
'a','b','c','x,y,z'

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-02 : 13:54:44

I think I can do this with the help of right function, trying that now...hmm

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-02 : 13:57:11

need to search a string ",'" from right till I find ",'" then eliminate that part of the string

-Neil

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-07-02 : 13:59:03

If all you want to do is identify the row, use

WHERE c4 like '%,%'

Jim

Everyday I learn something that somebody else already knew

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-02 : 14:27:24

Thanks Jim,

I am looping through all the records one by one and holding entire row
['a','b','c','x,y,z'] into one variable, here I need to separate it

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-02 : 15:01:32

how can I identify ,' from right using charindex or patindex is it possible, to separate the string based on this?

-Neil

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-02 : 16:08:21

SELECT t.c1, t.c2, t.c3, f.Data AS c4
FROM dbo.Table1 AS t
CROSS APPLY dbo.fnParseList(t.c4, ',') AS f

N 56°04'39.26"
E 12°55'05.63"

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-03 : 03:56:26

Thanks Peso,

I have data in the table T as

'a','b','c','d'
'a','b','c','x,y,z'

c1 c2
-----------
1 'a','b','c','d'
2 'a','b','c','x,y,z'
.
.
.
.
so on..

I have looped it using id and examining each col2 of the data to find the patter, the target table has 4 columns, which is created on fly in the sp.

now i need to split up data like this 'a','b','c','x,y,z'

to var1='a','b','c'
and var2 = 'x'

in first cycle
and in next cycle

to var1='a','b','c'
and var2 = 'y'

and so on, here I have done everything rt except I am not able to split the data as needed for the rec like this to be inserted 'a','b','c','x,y,z'

could you help me in splitting this

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-03 : 12:13:12

Hi Sunitha,

I have posted the SP at this link, in a separate thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176173&whichpage=2

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources