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.
Author |
Topic |
gongxia649
So Suave
344 Posts |
Posted - 2006-11-27 : 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
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-27 : 22:26:33
|
How about?Select '-' as Col1, '-' as Col2, '-' as Col3, '-' as Col4, '-' as Col5 Harsh AthalyeIndia."Nothing is Impossible" |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2006-11-27 : 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
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-27 : 23:49:07
|
What is a pregnant query?Harsh AthalyeIndia."Nothing is Impossible" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-28 : 00:37:11
|
Use nested CHARINDEX functions.Peter LarssonHelsingborg, Sweden |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-11-28 : 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..Chiraghttp://chirikworld.blogspot.com/ |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-28 : 01:32:37
|
Or you can use derived tables-- prepare test datadeclare @t table (r varchar(200))insert @tselect 'wopipwier-lmklsdje-sdgbre-;erlsl;k-wertwer-wieroiu'-- do the workselect [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) part6FROM ( 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 LarssonHelsingborg, Sweden |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-11-28 : 02:02:40
|
OR May Somthing like this for the split function http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648Select 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..Chiraghttp://chirikworld.blogspot.com/ |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-28 : 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) Col6From ( 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 LarssonHelsingborg, Sweden |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-11-28 : 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 LarssonHelsingborg, 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..Chiraghttp://chirikworld.blogspot.com/ |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-11-28 : 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 NormalizationMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|