Sorting with Varchar Field

Question

Sorting with Varchar Field

Viewing 9 posts - 16 through 23 (of 23 total)

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

Calvin Lawson SSChampion Points: 11030 More actions · Answer 1

Yoda's advice is wise and illustrates the real problem, in that your column is an aggregate of two values.

Frank, love the pattern searching for this. My only gripe is that your function is not set based. Herb, your assuming that all numbers will be on the right, but what if you couldn't?

What do you think of this? Trivial or useful?

create table Character

(Value char(1) Primary Key)

Insert Character values ('A')

Insert Character values ('B')

Insert Character values ('C')

Insert Character values ('D')

Insert Character values ('E')

Insert Character values ('F')

Insert Character values ('G')

Insert Character values ('H')

Insert Character values ('I')

Insert Character values ('J')

Insert Character values ('K')

Insert Character values ('L')

Insert Character values ('M')

Insert Character values ('N')

Insert Character values ('O')

Insert Character values ('P')

Insert Character values ('Q')

Insert Character values ('R')

Insert Character values ('S')

Insert Character values ('T')

Insert Character values ('U')

Insert Character values ('V')

Insert Character values ('W')

Insert Character values ('X')

Insert Character values ('Y')

Insert Character values ('Z')

create function RemoveChars (@Input varchar(8000))

returns int

as

begin

declare @Int int

select @Input = replace(@Input, Value, '')

From Character

select @int = Case isnumeric (@Input)

When 1 then @Input

Else null

END

return @int

end

select dbo.RemoveChars('10A99')

Signature is NULL

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Frank, love the pattern searching for this. My only gripe is that your function is not set based.

Yes, that's right, but the data doesn't really seems to be normalized. So, for every time there is a season.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sunny-141957 Right there with Babe Points: 796 More actions · Answer 3

Dear Friends,

Thank U so much for u'r replies.

Everyone of u'r replies where very informative.I could solve the problem by the logic given by "Frank Kalis".Thanks for that.

I just couldn't believe when i got so many answers from u friends.That shows that you guys/gals were so much involved in helping me solve my problem.

Hope to get more help from u friends in the future too.

regards

shown_sunny

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 4

Guys, guys, guys,

Pls take a look at the data, then re-create it. A normal, simple ORDER BY works perfectly well when I run it.

Results (as posted earlier):

RKA10

RKA100

RKA11

RMX10

RMX100

RMX11

RSA12

RSA120

RSA139

RSBH01

As you and I would expect.

The problem is Sunny's (?) result set doesnt match what I (and I would expect 99% of you) get, running the same query. Sunny's results with an ORDER BY:

RKA10

RKA100

RKA11

RMX10

RMX100

RMX11

RSA139

RSA12

RSA120

RSBH01

If the results from a single sql query differ from 1 machine to everybody else's, surely this is more of an oddball / configuration / data problem. Unless of course the sql is different.

Working around the problem in code will not solve the base problem and it wont go away, so Sunny will always have to work around the problem.

Could you pls post the full sql statement you are using.

Have fun

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Steve,

time to wake up.

This is yours

This was required

RKA10

RKA100

RKA11

You see the difference

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Calvin,

nice and cute one!

May I add this to my site, so the users can decide for themselves which one to use?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 7

My mind is in the twighlight zone.

Another one of those friday feelings... This is what happens when 'they' swop you over from 2 months solid sql and expect VBScripts coming out your ears on the same morning.

Daren't put any Friday funnies in after last weeks performance - so, I will just wish you all a food weekend. Hopefully I will come back refreshed on Monday.....

Have a Good Weekend all.

Steve

We need men who can dream of things that never were.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 8

May I add this to my site, so the users can decide for themselves which one to use?

Sure, feel free to post it, but I think the patindex method is probably the most optimal. Of course, my method can be used to with any subset of actual characters (remove only vowels, say) , so it's probably useful to.

Signature is NULL

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Thanks!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]