String Query - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

String Query

Author

Topic

kjmraohyd
Starting Member

34 Posts

Posted - 2006-06-16 : 02:13:42

Hi I have the following text come from TableA.Col1 and I need to sbstring and insert into TableB in 4 columns. I tried Substring and charindex but of no luck.

Source:

Col1
11/11/2005 14:06:25 [PIM] - Left message 24/05/2005 14:13:07 [PIM] - Left voicemail

Destination
col1 Col2 Col3 Col4
11/11/2005 14:06:25 [PIM] Left message
24/05/2005 14:13:07 [PIM] Left voicemail

TIA

Jagan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-16 : 02:22:38

is it always [PIM] or there are other codes ?

kjmraohyd
Starting Member

34 Posts

Posted - 2006-06-16 : 02:35:54

No that can be different words like [PIM],[JD] etc....

Jagan

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 02:51:00

is that left is constant or may be a diffrent..?

kjmraohyd
Starting Member

34 Posts

Posted - 2006-06-16 : 02:55:02

The fourth column is long text and this can be anything. For Example

03/08/2004 15:42:43 [JAC] - 5 packs done - 3/8/04

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 02:58:07

See second version at end of thread.

I can't tell what kind of layout your destination table has. If above code is not working, you can CONVERT the values in the last loop, where data is transferred from stage table to destination table, as CONVERT(datetime, left(@rowinfo, 10)).

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 03:28:05

See the results as per u want

Select
Substring('11/11/2005 14:06:25 [PIM] Left message',1,10),
substring('11/11/2005 14:06:25 [PIM] Left message', 12, 8),
substring('11/11/2005 14:06:25 [PIM] Left message', 21, charindex(']', '11/11/2005 14:06:25 [PIM] Left message') - 20),
right('11/11/2005 14:06:25 [PIM] Left message', len('11/11/2005 14:06:25 [PIM] Left message') - charindex(']', '11/11/2005 14:06:25 [PIM] Left message') - 1)

Select
Substring('11/11/2005 14:06:25 [JD] krishnakumar.C',1,10),
substring('11/11/2005 14:06:25 [JD] krishnakumar.C', 12, 8),
substring('11/11/2005 14:06:25 [JD] krishnakumar.C', 21, charindex(']', '11/11/2005 14:06:25 [JD] krishnakumar.C') - 20),
right('11/11/2005 14:06:25 [JD] krishnakumar.C', len('11/11/2005 14:06:25 [JD] krishnakumar.C') - charindex(']', '11/11/2005 14:06:25 [JD] krishnakumar.C') - 1)

Select
Substring('11/11/2005 14:06:25 [FUN With] Microsoft Sqlserver 2000',1,10),
substring('11/11/2005 14:06:25 [FUN With] Microsoft Sqlserver 2000', 12, 8),
substring('11/11/2005 14:06:25 [FUN With] Microsoft Sqlserver 2000', 21, charindex(']', '11/11/2005 14:06:25 [FUN With] Microsoft Sqlserver 2000') - 20),
right('11/11/2005 14:06:25 [FUN With] Microsoft Sqlserver 2000', len('11/11/2005 14:06:25 [FUN With] Microsoft Sqlserver 2000') - charindex(']', '11/11/2005 14:06:25 [FUN With] Microsoft Sqlserver 2000') - 1)

kjmraohyd
Starting Member

34 Posts

Posted - 2006-06-16 : 03:39:15

Hi Pete,

Many Many Thanks... It worked for me.

Jagan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 03:53:34

quote:
Originally posted by CSK

SELECT Substring('11/11/2005 14:06:25 [PIM] Left message',1,10),
substring('11/11/2005 14:06:25 [PIM] Left message', 12, 8),
substring('11/11/2005 14:06:25 [PIM] Left message', 21, charindex(']', '11/11/2005 14:06:25 [PIM] Left message') - 20),
right('11/11/2005 14:06:25 [PIM] Left message', len('11/11/2005 14:06:25 [PIM] Left message') - charindex(']', '11/11/2005 14:06:25 [PIM] Left message') - 1)

Are you sure rows always has two pieces of information?

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 03:55:54

quote:
Originally posted by Peso

quote:
Originally posted by CSK

Substring('11/11/2005 14:06:25 [PIM] Left message',1,10),
substring('11/11/2005 14:06:25 [PIM] Left message', 12, 8),
substring('11/11/2005 14:06:25 [PIM] Left message', 21, charindex(']', '11/11/2005 14:06:25 [PIM] Left message') - 20),
right('11/11/2005 14:06:25 [PIM] Left message', len('11/11/2005 14:06:25 [PIM] Left message') - charindex(']', '11/11/2005 14:06:25 [PIM] Left message') - 1)
Are you sure rows always has two pieces of information?

Peter Larsson
Helsingborg, Sweden

I cant get u

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 04:02:25

What if one of the rows in the source table has FOUR pieces of information and some other line only has 2 pieces of information?

See

Col1
11/11/2005 14:06:25 [PIM] - Left message 24/05/2005 14:13:07 [PIM] - Left voicemail 11/12/2006 02:12:25 [Alert] - Emergency call 31/12/2006 14:13:07 [Personal] - Happy new year!
11/11/2001 19:55:25 [PIM] - Left message 01/01/2001 00:00:05 [PIM] - Left voicemail

Or only 1 piece? Or 25 pieces? My guts tell me that the header [PIM] and [Alert] denotes which system is behind information.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 04:10:46

quote:
Originally posted by kjmraohyd

Hi I have the following text come from TableA.Col1 and I need to sbstring and insert into TableB in 4 columns. I tried Substring and charindex but of no luck.

Source:

Col1
11/11/2005 14:06:25 [PIM] - Left message 24/05/2005 14:13:07 [PIM] - Left voicemail

Destination
col1 Col2 Col3 Col4
11/11/2005 14:06:25 [PIM] Left message
24/05/2005 14:13:07 [PIM] Left voicemail

TIA

Jagan

Normalisation?
Also refer this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=65290

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 04:32:41

quote:
Originally posted by Peso

What if one of the rows in the source table has FOUR pieces of information?

See
Col1
11/11/2005 14:06:25 [PIM] - Left message 24/05/2005 14:13:07 [PIM] - Left voicemail 11/12/2006 02:12:25 [Alert] - Emergency call 31/12/2006 14:13:07 [Personal] - Happy new year!
Or only 1 piece? Or 25 pieces?
No I Thought it's multiple Pieces from The source table

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 04:34:48

quote:
Originally posted by madhivanan

quote:
Originally posted by kjmraohyd

Hi I have the following text come from TableA.Col1 and I need to sbstring and insert into TableB in 4 columns. I tried Substring and charindex but of no luck.

Source:

Col1
11/11/2005 14:06:25 [PIM] - Left message 24/05/2005 14:13:07 [PIM] - Left voicemail

Destination
col1 Col2 Col3 Col4
11/11/2005 14:06:25 [PIM] Left message
24/05/2005 14:13:07 [PIM] Left voicemail

TIA

Jagan

Normalisation?
Also refer this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=65290

Madhivanan

Failing to plan is Planning to fail

Madhi, My openion this is not refered the normalisation.
kk

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 04:44:10

So you think TableA is normalised?

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 04:54:44

yeah! The same

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 05:01:12

Read this
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 05:11:43

Right Madhi, Ur Concept is Ok. I accpted. But Here The table contains only one column
here we need not to put any joins and can't put joins also. Just spliting the rows into other tables.

If i am wrong means please give me ur suggession

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 05:20:21

Thats what it is known as Normalisation

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 05:20:37

quote:
Originally posted by CSK

Just spliting the rows into other tables.

If i am wrong means please give me ur suggession

How do you know how many "splits" to do on column? This column could be filled with data from several other systems such as call-center systems, switch-boards and so on...
We don't know since the original poster didn't tell us. But it is fair to say there could be 1, 2, 3 and up to many splits. Do you agree?

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-06-16 : 05:26:51

Yes

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources