Character type number convert

Question

Post reply

Character type number convert

sampathsoft

SSC Eights!

Points: 855
More actions
July 13, 2010 at 4:04 am

#223766

In my table field records storing char type . That records like
1
1.1
1.1.3
1.2
1.2.1
1.2.2
1.2.3
1.3
1.3.1
1.3.2
1.3.3
1.3.3.1
I want to sort it like
1
1.1
1.1.3
1.2
1.3
1.2.1
1.2.2
1.2.3
1.3.1
1.3.2
1.3.3
1.3.3.1
But here i cant convert it to decimal or float. Cause some values has two or more (.) .
My purpose is to generate tree and find top level and there break down .
Please provide me some tec help.
Thanks

Viewing 15 posts - 1 through 15 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Something like this?

--Test data

DECLARE @TABLE AS TABLE(

data CHAR(7))

INSERT INTO @TABLE

SELECT '1'

UNION ALL SELECT '1.1'

UNION ALL SELECT '1.1.3'

UNION ALL SELECT '1.2'

UNION ALL SELECT '1.2.1'

UNION ALL SELECT '1.2.2'

UNION ALL SELECT '1.2.3'

UNION ALL SELECT '1.3'

UNION ALL SELECT '1.3.1'

UNION ALL SELECT '1.3.2'

UNION ALL SELECT '1.3.3'

UNION ALL SELECT '1.3.3.1'

--Query

SELECT *

FROM @TABLE

ORDER BY CASE

WHEN Len(data) = 3 THEN ( CONVERT(FLOAT, (Substring(data, 1, 3))) )

ELSE ( CONVERT(FLOAT, (Substring(data, 3, 3))) )

END

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150467 More actions · Answer 2

For interest's sake, here's a 2008 solution based on hierarchyid (which is a very useful type for tree problems):

DECLARE @TABLE

TABLE (

data VARCHAR(7) NOT NULL

);

INSERT @TABLE

(data)

VALUES ('1'),

('1.1'),

('1.1.3'),

('1.2'),

('1.2.1'),

('1.2.2'),

('1.2.3'),

('1.3'),

('1.3.1'),

('1.3.2'),

('1.3.3'),

('1.3.3.1');

SELECT data

FROM @TABLE

ORDER BY CONVERT(hierarchyid, '/' + REPLACE(data, '.', '/') + '/')

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

DROP TABLE #TABLE

CREATE TABLE #TABLE (data CHAR(7))

INSERT INTO #TABLE

SELECT '1'

UNION ALL SELECT '1.1'

UNION ALL SELECT '1.1.3'

UNION ALL SELECT '1.2'

UNION ALL SELECT '1.2.1'

UNION ALL SELECT '1.2.2'

UNION ALL SELECT '1.2.3'

UNION ALL SELECT '1.3'

UNION ALL SELECT '1.3.1'

UNION ALL SELECT '1.3.2'

UNION ALL SELECT '1.3.3'

UNION ALL SELECT '1.3.3.1'

SELECT *

FROM #TABLE

ORDER BY LEN(data), data

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul White SSC Guru Points: 150467 More actions · Answer 4

Where should 1.2.10 sort, I wonder...?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Paul White NZ (7/13/2010)
Where should 1.2.10 sort, I wonder...?

Or 1.1.3, even...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

Paul White NZ (7/13/2010)
Where should 1.2.10 sort, I wonder...?

You killed both solutions 😛

I'm thinking CHARINDEX, split on the period and sort that way?

Chris Morris-439714 (7/13/2010)
Or 1.1.3, even...

I think the OP specified the location of that.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150467 More actions · Answer 7

skcadavre (7/13/2010)
You killed both solutions 😛

It's a knack 😉

I'm thinking CHARINDEX, split on the period and sort that way?

That would work (and Chris would provide a recursive CTE solution)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 8

Chris Morris-439714 (7/13/2010)
Paul White NZ (7/13/2010)
Where should 1.2.10 sort, I wonder...?
Or 1.1.3, even...

Quite.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Paul White NZ (7/13/2010)
Chris Morris-439714 (7/13/2010)
Paul White NZ (7/13/2010)
Where should 1.2.10 sort, I wonder...?
Or 1.1.3, even...
Quite.

Sampathsoft, please can you confirm what position 1.1.3 would assume in your sequence?

Many thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Paul White NZ (7/13/2010)
skcadavre (7/13/2010)
You killed both solutions 😛
It's a knack 😉
I'm thinking CHARINDEX, split on the period and sort that way?
That would work (and Chris would provide a recursive CTE solution)

It would be chasing my tail to argue with that 🙂

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Cadavre SSC-Forever Points: 41690 More actions · Answer 11

Chris Morris-439714 (7/13/2010)
Paul White NZ (7/13/2010)
Chris Morris-439714 (7/13/2010)
Paul White NZ (7/13/2010)
Where should 1.2.10 sort, I wonder...?
Or 1.1.3, even...
Quite.
Sampathsoft, please can you confirm what position 1.1.3 would assume in your sequence?
Many thanks.

sampathsoft (7/13/2010)
I want to sort it like
1
1.1
1.1.3
1.2
1.3
1.2.1
1.2.2
1.2.3
1.3.1
1.3.2
1.3.3
1.3.3.1

I'd write something myself, but I've got 4 minutes left of my break, so I leave it in your much more capable hands.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Paul White SSC Guru Points: 150467 More actions · Answer 12

skcadavre (7/13/2010)
Paul White NZ (7/13/2010)
Where should 1.2.10 sort, I wonder...?
Chris Morris-439714 (7/13/2010)
Or 1.1.3, even...
I think the OP specified the location of that.

He did.

I took Chris' post as pointing out that his code did not sort 1.1.3 as required.

That aside, I think the OP is going to have wider problems with this sort of 'materialised path' representation in 2K5. It's usually easier to store the data as an adjacency list, or nested sets, and generate the path representation as and when required.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Paul White NZ (7/13/2010)
skcadavre (7/13/2010)
Paul White NZ (7/13/2010)
Where should 1.2.10 sort, I wonder...?
Chris Morris-439714 (7/13/2010)
Or 1.1.3, even...
I think the OP specified the location of that.
He did.
I took Chris' post as pointing out that his code did not sort 1.1.3 as required.
That aside, I think the OP is going to have wider problems with this sort of 'materialised path' representation in 2K5. It's usually easier to store the data as an adjacency list, or nested sets, and generate the path representation as and when required.

Oh no, I knew I should have stayed at home today with this headsnake :doze:

This looks to me like a valid sequence with rules - note the position of 1.1.3. I can't make sense of it with 1.1.3 in the position the OP specified.

1

1.1

1.2

1.3

1.1.3

1.2.1

1.2.2

1.2.3

1.3.1

1.3.2

1.3.3

1.3.3.1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul White SSC Guru Points: 150467 More actions · Answer 14

Chris Morris-439714 (7/13/2010)
Oh no, I knew I should have stayed at home today with this headsnake :doze:
This looks to me like a valid sequence with rules - note the position of 1.1.3. I can't make sense of it with 1.1.3 in the position the OP specified.

This may help untangle your headsnake:

http://communities.bmc.com/communities/docs/DOC-9902

(or it may not)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi