Parse Column and Sort

Hi can anyone give your idea on how this can be implemented.
I have a table which has a column whose values looks like
Page 10 Line 1
Document
1
3
Sheet: Landscape
Sheet: Part 2
Page: 29 Line: 2
Page91
Page 10
Sheet: Sheet1
i need to sort by Page number and line number if no line number then page
number alone or if no page number then sort by sheet number else normal sort
Can anyone throw some light on itBoss
Why not do that on client?
CREATE TABLE #Test
(
col1 VARCHAR(15)
)
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Document')
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('3')
INSERT INTO #Test VALUES ('Sheet: Landscape')
INSERT INTO #Test VALUES ('Page: 29 Line: 2')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page91')
INSERT INTO #Test VALUES ('Page 10')
INSERT INTO #Test VALUES ('Sheet: Sheet1')
SELECT * FROM #Test ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END DESC
"Boss" <Boss@.discussions.microsoft.com> wrote in message
news:E77C9A37-3E0B-4847-AB07-D7B0C976C5D0@.microsoft.com...
> Hi can anyone give your idea on how this can be implemented.
> I have a table which has a column whose values looks like
> Page 10 Line 1
> Document
> 1
> 3
> Sheet: Landscape
> Sheet: Part 2
> Page: 29 Line: 2
> Page91
> Page 10
> Sheet: Sheet1
> i need to sort by Page number and line number if no line number then page
> number alone or if no page number then sort by sheet number else normal
sort
>
> Can anyone throw some light on it
>|||Can't you clean up the data and create a table where these attributes
are properly represented in atomic columns? Storing meaningful data in
this form is an extremely poor and inconvenient design. That's why it's
usual to scrub and transform external data before it is loaded into
tables.
If a redesign is impossible for you then take a look at CHARINDEX and
PATINDEX. You can extract the different string components into separate
calculated columns and then sort on those.
If you need more help, please post DDL (CREATE TABLE statement
including keys and constraints), some sample data (as INSERT
statements) and show your required end result.
David Portas
SQL Server MVP
--|||Hi Thanks for your reply
It dosent seem to return correct records
CREATE TABLE #Test
(
col1 VARCHAR(25)
)
-- drop table #test
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Document')
INSERT INTO #Test VALUES ('1')
INSERT INTO #Test VALUES ('3')
INSERT INTO #Test VALUES ('Sheet: Landscape')
INSERT INTO #Test VALUES ('Page: 29 Line: 2')
INSERT INTO #Test VALUES ('Page: 29 Line: 1')
INSERT INTO #Test VALUES ('Page: 29 Line: 10')
INSERT INTO #Test VALUES ('Page: 2 Line: 18')
INSERT INTO #Test VALUES ('Page: 71 Line: 1')
INSERT INTO #Test VALUES ('Page: 201 Line: 1000')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page 10 Line 1')
INSERT INTO #Test VALUES ('Page91')
INSERT INTO #Test VALUES ('Page1')
INSERT INTO #Test VALUES ('Page 20')
INSERT INTO #Test VALUES ('Page 10')
INSERT INTO #Test VALUES ('Page 8')
INSERT INTO #Test VALUES ('Sheet: Sheet1')
INSERT INTO #Test VALUES ('Sheet: Sheet21')
INSERT INTO #Test VALUES ('Sheet: Sheet10')
SELECT * FROM #Test ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END ASC
The result is
Document
1
3
Sheet: Landscape
Sheet: Sheet1
Sheet: Sheet21
Sheet: Sheet10
Page: 201 Line: 1000 - this is in wrong order
Page1
Page 8
Page91
Page 10
Page 20
Page 10 Line 1
Page 10 Line 1
Page 10 Line 1
Page: 2 Line: 18 - this is in wrong order
Page: 29 Line: 1
Page: 29 Line: 2
Page: 71 Line: 1 - this is in wrong order
Page: 29 Line: 10
"Uri Dimant" wrote:

> Boss
> Why not do that on client?
> CREATE TABLE #Test
> (
> col1 VARCHAR(15)
> )
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Document')
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('3')
> INSERT INTO #Test VALUES ('Sheet: Landscape')
> INSERT INTO #Test VALUES ('Page: 29 Line: 2')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page91')
> INSERT INTO #Test VALUES ('Page 10')
> INSERT INTO #Test VALUES ('Sheet: Sheet1')
> SELECT * FROM #Test ORDER BY CASE WHEN
> CHARINDEX('Line',col1)>0 OR
> CHARINDEX('Page',col1)>0
> THEN RIGHT('000000000000000'+col1,15)
> END DESC
>
>
>
> "Boss" <Boss@.discussions.microsoft.com> wrote in message
> news:E77C9A37-3E0B-4847-AB07-D7B0C976C5D0@.microsoft.com...
> sort
>
>|||SELECT col1 FROM
(
SELECT replace(col1,' ','')as col1 FROM #Test
) AS Der
ORDER BY CASE WHEN
CHARINDEX('Line',col1)>0 OR
CHARINDEX('Page',col1)>0
THEN RIGHT('000000000000000'+col1,15)
END ASC
If it does not help ,post expected result, as I and David stated you will be
better off to do that on the client side, but befor that clean up the table
from redundant data
"Boss" <Boss@.discussions.microsoft.com> wrote in message
news:B175FA7A-14E9-4A57-A018-F7B48C805937@.microsoft.com...
> Hi Thanks for your reply
> It dosent seem to return correct records
> CREATE TABLE #Test
> (
> col1 VARCHAR(25)
> )
> -- drop table #test
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Document')
> INSERT INTO #Test VALUES ('1')
> INSERT INTO #Test VALUES ('3')
> INSERT INTO #Test VALUES ('Sheet: Landscape')
> INSERT INTO #Test VALUES ('Page: 29 Line: 2')
> INSERT INTO #Test VALUES ('Page: 29 Line: 1')
> INSERT INTO #Test VALUES ('Page: 29 Line: 10')
> INSERT INTO #Test VALUES ('Page: 2 Line: 18')
> INSERT INTO #Test VALUES ('Page: 71 Line: 1')
> INSERT INTO #Test VALUES ('Page: 201 Line: 1000')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page 10 Line 1')
> INSERT INTO #Test VALUES ('Page91')
> INSERT INTO #Test VALUES ('Page1')
> INSERT INTO #Test VALUES ('Page 20')
> INSERT INTO #Test VALUES ('Page 10')
> INSERT INTO #Test VALUES ('Page 8')
> INSERT INTO #Test VALUES ('Sheet: Sheet1')
> INSERT INTO #Test VALUES ('Sheet: Sheet21')
> INSERT INTO #Test VALUES ('Sheet: Sheet10')
> SELECT * FROM #Test ORDER BY CASE WHEN
> CHARINDEX('Line',col1)>0 OR
> CHARINDEX('Page',col1)>0
> THEN RIGHT('000000000000000'+col1,15)
> END ASC
> The result is
> Document
> 1
> 3
> Sheet: Landscape
> Sheet: Sheet1
> Sheet: Sheet21
> Sheet: Sheet10
> Page: 201 Line: 1000 - this is in wrong order
> Page1
> Page 8
> Page91
> Page 10
> Page 20
> Page 10 Line 1
> Page 10 Line 1
> Page 10 Line 1
> Page: 2 Line: 18 - this is in wrong order
> Page: 29 Line: 1
> Page: 29 Line: 2
> Page: 71 Line: 1 - this is in wrong order
> Page: 29 Line: 10
>
> "Uri Dimant" wrote:
>
page
normal