| Author |
 Topic  |
|
|
gongxia649
So Suave
Azores
344 Posts |
 Posted - 11/27/2006 : 21:46:53
|
there are 5 hyphens. want to find it one by one.
set:
select ('wopipwier-lmklsdje-sdgbre-;erlsl;k-wertwer-wieroiu')
result:
col1|col2|col3 ....so on
- - -
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 11/27/2006 : 22:26:33
|
How about?
Select
'-' as Col1,
'-' as Col2,
'-' as Col3,
'-' as Col4,
'-' as Col5
Harsh Athalye
India.
"Nothing is Impossible" |
 |
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 11/27/2006 : 23:01:54
|
is that a pregnant query? how can you tell?
SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org |
|
|
|
harsh_athalye
Flowing Fount of Yak Knowledge
India
5509 Posts |
Posted - 11/27/2006 : 23:49:07
|
What is a pregnant query?
Harsh Athalye
India.
"Nothing is Impossible" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/28/2006 : 00:37:11
|
Use nested CHARINDEX functions.
Peter Larsson
Helsingborg, Sweden |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 11/28/2006 : 01:31:26
|
quote:
Originally posted by gongxia649
there are 5 hyphens. want to find it one by one.
set:
select ('wopipwier-lmklsdje-sdgbre-;erlsl;k-wertwer-wieroiu')
result:
col1|col2|col3 ....so on
- - -
There are more then 5 hypens in the sample data which you have provided..
Can you post the right data with your sample output then surely someone over here can help you out..
Chirag
http://chirikworld.blogspot.com/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/28/2006 : 01:32:37
|
Or you can use derived tables-- prepare test data
declare @t table (r varchar(200))
insert @t
select 'wopipwier-lmklsdje-sdgbre-;erlsl;k-wertwer-wieroiu'
-- do the work
select [1],
[2],
[3],
[4],
[5],
SUBSTRING(r, 1, [1] - 1) part1,
SUBSTRING(r, [1] + 1, [2] - [1] - 1) part2,
SUBSTRING(r, [2] + 1, [3] - [2] - 1) part3,
SUBSTRING(r, [3] + 1, [4] - [3] - 1) part4,
SUBSTRING(r, [4] + 1, [5] - [4] - 1) part5,
SUBSTRING(r, [5] + 1, 8000) part6
FROM (
select r,
[1],
[2],
[3],
[4],
[4] + charindex('-', substring(r, [4] + 1, 8000)) [5]
from (
select r,
[1],
[2],
[3],
[3] + charindex('-', substring(r, [3] + 1, 8000)) [4]
from (
select r,
[1],
[2],
[2] + charindex('-', substring(r, [2] + 1, 8000)) [3]
from (
select r,
[1],
[1] + charindex('-', substring(r, [1] + 1, 8000)) [2]
from (
select r,
CHARINDEX('-', r) [1]
from @t
) a
) b
) c
) d
) e
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 11/28/2006 01:34:13 |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 11/28/2006 : 02:02:40
|
OR May Somthing like this
for the split function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
Select MAx(Col1) As Col1 ,Max(Col2) Col4 ,MAx(Col3) Col3 ,Max(Col4) Col4 From
(
Select
Case When [DATA] = 'wopipwier' Then [DATA] End As Col1,
Case When [DATA] = 'lmklsdje' Then [DATA] End As Col2,
Case When [DATA] = 'sdgbre' Then [DATA] End As Col3,
Case When [DATA] = ';erlsl;k' Then [DATA] End As Col4
FRom Dbo.Split('wopipwier-lmklsdje-sdgbre-;erlsl;k-wertwer-wieroiu','-')
) as f
also after you can do the dynamic queries... to get this result..
Chirag
http://chirikworld.blogspot.com/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 11/28/2006 : 02:05:54
|
I don't think you mean to hardwire the DATA, right?  Select MAx(Col1) As Col1, Max(Col2) Col2, Max(Col3) Col3, Max(Col4) Col4, Max(Col5) Col5, Max(Col6) Col6
From
(
Select
Case When ID = 1 Then [DATA] End As Col1,
Case When ID = 2 Then [DATA] End As Col2,
Case When ID = 3 Then [DATA] End As Col3,
Case When ID = 4 Then [DATA] End As Col4,
Case When ID = 5 Then [DATA] End As Col5,
Case When ID = 6 Then [DATA] End As Col6
FRom Dbo.Split('wopipwier-lmklsdje-sdgbre-;erlsl;k-wertwer-wieroiu', '-')
) as f
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 11/28/2006 02:07:41 |
|
|
|
chiragkhabaria
Flowing Fount of Yak Knowledge
India
1907 Posts |
Posted - 11/28/2006 : 02:08:29
|
quote:
Originally posted by Peso
I don't think you mean to hardwire the DATA, right? Select MAx(Col1) As Col1 ,Max(Col2) Col4 ,MAx(Col3) Col3 ,Max(Col4) Col4 From
(
Select
Case When ID = 1 Then [DATA] End As Col1,
Case When ID = 2 Then [DATA] End As Col2,
Case When ID = 3 Then [DATA] End As Col3,
Case When ID = 4 Then [DATA] End As Col4
FRom Dbo.Split('wopipwier-lmklsdje-sdgbre-;erlsl;k-wertwer-wieroiu', '-')
) as f
Peter Larsson
Helsingborg, Sweden
aha not really thatz why at the end i wrote this can be achive by the dynamic way too.... like a normal pivot queries...
I like your solution but if one - hypen increases you have to right one more level for sub query.. so what just wondering with it..
Chirag
http://chirikworld.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 11/28/2006 : 04:28:30
|
quote:
Originally posted by gongxia649
there are 5 hyphens. want to find it one by one.
set:
select ('wopipwier-lmklsdje-sdgbre-;erlsl;k-wertwer-wieroiu')
result:
col1|col2|col3 ....so on
- - -
Where do you want to show data?
If you use front end application, use split function there. Also if you store values seperated by hyphen, you need to read about Normalization
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
|
|
want to find these hyphens.
