Character type number convert

Question

Post reply

Character type number convert

Viewing 9 posts - 31 through 38 (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

Right, I cheated a bit 😛

--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'

SELECT *

FROM @TABLE

ORDER BY CASE

WHEN Substring(data, 3, 1) = 1 THEN ( Charindex('.', data) )

ELSE Len(data)

END,data

/*

data

-------

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

*/

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/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

your litle "cheat" will only work for cases where the second part of data is less than 10 .

Check what will happen if you add 1.10 or 2.10 in a list.

Probably I missed something. But I can't see how this list:

1.0

1.1

1.1.3

1.2

1.3

1.2.1

Represent the sort hierarchy? Why 1.1.3 goes after 1.1 but 1.2.1 is not after 1.2?

Have you tried the function I've posted (Post #951618, bottom of page 3)? So far, I could not find cases breaking it.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

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

Eugene Elutin (7/14/2010)
your litle "cheat" will only work for cases where the second part of data is less than 10 .
Check what will happen if you add 1.10 or 2.10 in a list.
Probably I missed something. But I can't see how this list:
1.0
1.1
1.1.3
1.2
1.3
1.2.1
Represent the sort hierarchy? Why 1.1.3 goes after 1.1 but 1.2.1 is not after 1.2?
Have you tried the function I've posted (Post #951618, bottom of page 3)? So far, I could not find cases breaking it.

What can I say? The OP has a funny order 😉

My cheat works for the sample data and was more meant for amusement value than as an actual answer.

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 4

skcadavre (7/14/2010)
What can I say? The OP has a funny order 😉

...and a wrong one at that :doze:

skcadavre (7/14/2010)
My cheat works for the sample data and was more meant for amusement value than as an actual answer.

I got that - I smirked and moved on. Maybe I should have posted a :laugh: and a :w00t: and a 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Paul, have you seen my post #951618 (using UDF), bottom of page 3?

Do you think this approach is viable for most of scenarious? Of course, this function better to be implemented as CLR.

I canot see any logic in the sort order requested by OP as well 😎

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

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

Eugene Elutin (7/14/2010)
Paul, have you seen my post #951618 (using UDF), bottom of page 3?
Do you think this approach is viable for most of scenarious? Of course, this function better to be implemented as CLR.

I did see it, but I didn't test it or look at it too closely because:

(a) I have an allergy to multi-statement TVFs (yes, a SQLCLR TVF would perform better);

(b) I trust your code to work as advertised; and

(c) I still think the requested order is daft and wrong, so it's all a bit moot

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

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

Paul White NZ (7/14/2010)
Eugene Elutin (7/14/2010)
Paul, have you seen my post #951618 (using UDF), bottom of page 3?
Do you think this approach is viable for most of scenarious? Of course, this function better to be implemented as CLR.
I did see it, but I didn't test it or look at it too closely because:
(a) I have an allergy to multi-statement TVFs (yes, a SQLCLR TVF would perform better);
(b) I trust your code to work as advertised; and
(c) I still think the requested order is daft and wrong, so it's all a bit moot
Paul

(d) The OP is AWOL

^{“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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

The simplest CLR in C# would look like:

public string UnwrapHierarchy(string value)

{

string [] hparts = value.Split('.');

for (int i = 0; i < hparts.Length; i++)

{

hparts = new string('1', Int32.Parse(hparts));

}

return String.Join(".", hparts);

}

However it is possible to write it in much more efficient way...

Actually my aproach returns it in the normal hierarchical order:

1

1.1

1.1.1

1.1.2

1.2

1.2.1

1.3

1.3.1

1.3.2

2

2.1

etc.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

gofrancesc Hall of Fame Points: 3269 More actions · Answer 9

Hi all,

sampathsoft, I suppose that in your desired order 1.2.3 is placed before 1.2.10, right?.

If you want to manage a tree structure you need to know what is the parent of each node; I hope this code can help you

CREATE TABLE #TABLE (data VarCHAR(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.2.10'

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 data

, CASE charindex('.', data)

WHEN 0 then NULL

ELSE left(data, len(data) - charindex('.', reverse(data)))

END AS Parent

, CAST(

CASE charindex('.', data)

WHEN 0 then data

ELSE right(data, charindex('.', reverse(data)) - 1)

END

AS INTEGER) AS NodeNumber

FROM #TABLE

ORDER BY Parent, NodeNumber

DROP TABLE #TABLE

Next you should decide if you will generate the parent nodes and node numbers on each execution or you will calculate these values only the first time and store them into your database.

Regards,

Francesc