May 11, 2011 at 1:37 am
Hi folks,
I am in critical position any one can help me from this condition . Actual my problem is in my table there is a nvarchar type data which having data like
1
0.2a
1a
a.2
z
a
001
008.003
i need output as
0.2a
1
1
1a
8.003
a
a.2
z
Thanks in advance
Seshu
May 11, 2011 at 2:11 am
Basically the design of the table is Wrong,i am not sure what is the perpose of this type of column
you can try like this
Create Table Table1(column1 nvarchar(10))
Insert Into Table1 select '1'
Insert Into Table1 select '0.2a'
Insert Into Table1 select '1a'
Insert Into Table1 select 'a.2'
Insert Into Table1 select 'z'
Insert Into Table1 select 'a'
Insert Into Table1 select '001'
Insert Into Table1 select '008.003'
Select * from Table1
Select REPLACE(LTRIM(REPLACE(column1, '0', ' ')), ' ', '0') From Table1
order by column1 asc
this will work if we don't have any spaces in the input text
basically i am replacing all the Zeros with the space and then trimming the leading space and then adding replacing the space back with the Zeros.
May 11, 2011 at 2:19 am
37
38
39
40
40
43
44
45
47
96
98
99
1
1
1
1
1
10
10
10
10
102
103
10c
11
11
11
11
110
12
12
12
12
1233333
13
13
13
13
14
14
14
14
15
15
15
15
15a
15b
15b
15c
15c
15d
15e
16
16
16
16
17
17
17a
17b
17c
17d
17e
18
18
18
19
19
19
19.1
19.2
1A
2
2
2
2
2.5
20
20.1
20.1
20.2
20.2
up to some instance the sort is working after that again breaks...
please help me ...
Thanks
seshu
May 11, 2011 at 2:20 am
may be you can use some thing like this
select substring(column1,patindex('%[^0]%',column1),8000)
from Table1
May 11, 2011 at 2:41 am
sorry it is also wont works is there any alternate way...
May 11, 2011 at 3:08 am
select * from (Select REPLACE(LTRIM(REPLACE(cm_submittal_no, '0', ' ')), ' ', '0') orginal
From prj_detail_submittal order by cm_submittal_no) asc
is this query works .... please let me know...
May 11, 2011 at 4:17 am
Please describe the rule of sorting.
We really have little of idea what we have to implement here.
_____________
Code for TallyGenerator
May 11, 2011 at 4:57 am
it ooks like the desired sorting is "strip non numeric chars and order by the numeric portion(if it exists) then by the string"
so
ORDER BY
CASE
WHEN ISNUMERIC(dbo.StripnonNumeric(ColName)) = 1
THEN CONVERT(DECIMAL(19,4),dbo.StripnonNumeric(ColName))
ELSE 999999
END,ColName
and one of many example funcrions;
CREATE FUNCTION StripNonNumeric(@OriginalText VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
DECLARE @CleanedText VARCHAR(8000)
;WITH tally (N) as
(SELECT TOP 10000 row_number() OVER (ORDER BY sc1.id)
FROM Master.dbo.SysColumns sc1
CROSS JOIN Master.dbo.SysColumns sc2)
SELECT @CleanedText = ISNULL(@CleanedText,'') +
CASE
--ascii numbers are 48(for '0') thru 57 (for '9')
WHEN ASCII(SUBSTRING(@OriginalText,Tally.N,1)) BETWEEN 48 AND 57 THEN SUBSTRING(@OriginalText,Tally.N,1) ELSE '' END
FROM tally WHERE Tally.N <= LEN(@OriginalText)
RETURN @CleanedText
END
Lowell
May 11, 2011 at 5:09 am
hi
Actually my table consist of different data like as i mentioned above it have int,float,char so i would like to sort that data is there any idea on it and in my table some values starts with 01,001,001 also
Thanks in Advance
Seshu
May 11, 2011 at 6:24 pm
seshukumar.thokala (5/11/2011)
hiActually my table consist of different data like as i mentioned above it have int,float,char so i would like to sort that data is there any idea on it and in my table some values starts with 01,001,001 also
Thanks in Advance
Seshu
We might have a lot of ideas - but it's you who has the actual task.
Describe what you need to achieve - then someone may come up with a solution.
The values in the column are obviously built from different parts having different meanings.
And you want to sort the outcome according to the meanings of those parts.
So, what you need to do is split the values to original parts (should not have concatenated them in the first place), possibly convert to appropriate data types and then order by those parts.
Sorry, it's not possible to do without understanding of the data, and you have not provided us with any help here.
_____________
Code for TallyGenerator
May 11, 2011 at 6:28 pm
Lowell (5/11/2011)
it ooks like the desired sorting is "strip non numeric chars and order by the numeric portion(if it exists) then by the string"
Your assumption is onviously wrong.
Look at this sequence from the example:
12
12
1233333
13
13
_____________
Code for TallyGenerator
May 11, 2011 at 11:48 pm
Hi ,
I am in critical position any one can help me from this condition . Actual my problem is in my table there is a nvarchar type data which having data like in datatable the data type is nvarchar
1
0.2a
1a
a.2
z
a
001
08
008.003
I need output as:
0.2a
1
001
1a
08
8.003
a
a.2
z
Thanks in advance
Seshu
May 11, 2011 at 11:50 pm
hi
In my Database the datatype is nvarchar and it consist of various types of data like as i mentioned so i like to sort that data in particular order is possible to do that..
Thanks in Advance
Seshu
May 12, 2011 at 3:54 am
Sergiy (5/11/2011)
Lowell (5/11/2011)
it ooks like the desired sorting is "strip non numeric chars and order by the numeric portion(if it exists) then by the string"Your assumption is onviously wrong.
Look at this sequence from the example:
12
12
1233333
13
13
nah, i'll stick with my guns on this one; the poster's not been consistent with his examples, but never provides concrete rules. when I look at what he posted, that 12333 value and a couple of other values appear to be included as example values, but overlooked as far as deciding the sorting rules that he's looking for; I still think my solution is what he wants,especially after the above posts, but hasn't tried it yet.
seshukumar.thokala i posted an example solution, did you try it? if you did try it, what did it not do, that you expected it to do?
we cannot help you if you post the same thing multiple times...you have to describe what you want, maybe in a different way.
Remember we all think logically, so if you can come up with the rule for sorting, tell us so we can help you better.
Lowell
May 12, 2011 at 3:58 am
Hi Lowell
I tried ur example wt u posted but my problem is in my database i am having data like 30.6.1 so how to sort these type of data ...
Thanks for spending ur valuable time...
Seshu
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply