Character type number convert

Question

Post reply

Character type number convert

Viewing 15 posts - 16 through 30 (of 38 total)

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

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

Looks like it's work:

DROP TABLE #TABLE

CREATE TABLE #TABLE (data CHAR(50))

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

UNION ALL SELECT '1.2.10'

UNION ALL SELECT '1.3'

UNION ALL SELECT '1.3.1'

UNION ALL SELECT '1.3.1.200'

UNION ALL SELECT '1.3.2'

UNION ALL SELECT '1.3.3'

UNION ALL SELECT '1.3.3.1'

UNION ALL SELECT '10'

UNION ALL SELECT '10.1'

UNION ALL SELECT '10.1.1'

UNION ALL SELECT '10.1.2'

SELECT DATA

,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(data,'2','11'),'3','111'),'4','1111'),'5','11111'),'6','111111'),'7','1111111'),'8','11111111'),'9','111111111'),'0','1111111111')

FROM #TABLE

ORDER BY

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(data,'2','11'),'3','111'),'4','1111'),'5','11111'),'6','111111'),'7','1111111'),'8','11111111'),'9','111111111'),'0','1111111111')

_____________________________________________
"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: 150468 More actions · Answer 2

Eugene Elutin (7/13/2010)
Looks like it's work:

Very creative.

Where would the following sort...?

1.4.1

1.4.2

1.5.1

1.5.2

1.22.1

1.23.1

😛

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

Thanks Paul, that was interesting - if challenging - reading. I've not yet used rCTEs to resolve hierarchies in anger. This would be a good place to start, given a little feedback from the OP.

@Eugene - creative, but the sequence doesn't match that required by the OP. Neither does mine 😀 ... yet.

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

I knew it wouldn't work, however it does look nice (as result of creative thinking :hehe:)... :w00t::-D:w00t:

_____________________________________________
"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: 150468 More actions · Answer 5

Eugene Elutin (7/13/2010)
I knew it wouldn't work, however it does look nice (as result of creative thinking

It does indeed look very pretty 😎

I'm going with the upgrade to 2008 and use my hierarchyid code idea :laugh:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

Paul White NZ (7/13/2010)
Eugene Elutin (7/13/2010)
I knew it wouldn't work, however it does look nice (as result of creative thinking
It does indeed look very pretty 😎
I'm going with the upgrade to 2008 and use my hierarchyid code idea :laugh:

It's great ... but...the order of the output doesn't match the order specified by the OP 😛

^{“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: 150468 More actions · Answer 7

Chris Morris-439714 (7/13/2010)
It's great ... but...the order of the output doesn't match the order specified by the OP 😛

Yeah, but the OP got it wrong!

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

Paul White NZ (7/13/2010)
Chris Morris-439714 (7/13/2010)
It's great ... but...the order of the output doesn't match the order specified by the OP 😛
Yeah, but the OP got it wrong!

TWICE! 😀

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

Looks like there is no simple way (set-based) to do it in SQL2005.

_____________________________________________
"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: 150468 More actions · Answer 10

Chris Morris-439714 (7/13/2010)
Paul White NZ (7/13/2010)
Chris Morris-439714 (7/13/2010)
It's great ... but...the order of the output doesn't match the order specified by the OP 😛
Yeah, but the OP got it wrong!
TWICE! 😀

:laugh:!!!

We need a better quality of OP. Perhaps we should start a thread about it...:w00t:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

Eugene Elutin (7/13/2010)
Looks like there is no simple way (set-based) to do it in SQL2005.

DBCC GetGapsInSpec

Depends on the fillfactor of the spec - if it's less than 50%, you have to resort to DBCC Timewarp.

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

Modifictaion of my creative way 😀

Requires a function (which, for a better performance, can be implemented in C#):

CREATE FUNCTION dbo.udf_CreativeThinkingWithOnes ( @sHV varchar(max) )

RETURNS VARCHAR(MAX)

WITH SCHEMABINDING

AS

BEGIN

DECLARE @sHVn VARCHAR(MAX)

DECLARE @DP INT

SET @sHVn = ''

SET @DP = CHARINDEX('.',@sHV)

WHILE @DP > 0

BEGIN

IF LEN(@sHVn) > 0 SET @sHVn = @sHVn + '.'

SET @sHVn = @sHVn + replicate('1',cast(left(@sHV, @DP - 1) as INT))

SET @sHV = SUBSTRING(@sHV, @DP + 1, LEN(@sHV))

SET @DP = CHARINDEX('.',@sHV);

END

IF LEN(@sHVn) > 0 SET @sHVn = @sHVn + '.'

SET @sHVn = @sHVn + replicate('1',cast(@sHV AS INT))

RETURN @sHVn

END

Now the test set (including 1.4.1, 1.22.1 and other):

DROP TABLE #TABLE

CREATE TABLE #TABLE (data CHAR(50))

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

UNION ALL SELECT '1.2.10'

UNION ALL SELECT '1.3'

UNION ALL SELECT '1.3.1'

UNION ALL SELECT '1.3.1.200'

UNION ALL SELECT '1.3.2'

UNION ALL SELECT '1.3.3'

UNION ALL SELECT '1.3.3.1'

UNION ALL SELECT '10'

UNION ALL SELECT '10.1'

UNION ALL SELECT '10.1.1'

UNION ALL SELECT '10.1.2'

UNION ALL SELECT '1.4.1'

UNION ALL SELECT '1.4.2'

UNION ALL SELECT '1.5.1'

UNION ALL SELECT '1.5.2'

UNION ALL SELECT '1.22.1'

UNION ALL SELECT '1.23.1'

SELECT DATA

,dbo.udf_CreativeThinkingWithOnes (data)

FROM #TABLE

ORDER BY

dbo.udf_CreativeThinkingWithOnes (data)

_____________________________________________
"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]

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 13

sampathsoft (7/13/2010)
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

I've got to ask... why wouldn't it be sorted like the following?

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

I also have to ask (just curious here), why do you want it sorted this way? It doesn't correctly reflect the hierarchy of the numbers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 14

Heh.... I've really got to learn to read the other posts before posting. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 15

Jeff Moden (7/14/2010)
Heh.... I've really got to learn to read the other posts before posting. :blush:

Where would be the fun in that? :laugh:

More seriously, it is a pet peeve of mine that this site frequently makes a thread look as if it only has a few posts - before you notice that the thread has more pages to it. The pagination code could use some work. Rant over.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi