Alphabetical Ordering Of Numeric Strings

Mar 27, 2007

Apologies if this has been done before, but I couldn't find a completed example. If anyone has time, I'd love to see some improvements...

Where's fribble when you need him?
/*
function:
numeric_order

arguments:
@numeric_string - a string of mixed alpha and numeric values
@max_digits- the maximum length of digits to compare

description:
Function numeric_order creates an orderable string based on the "numeric" value of @numeric_string
which can be ordered alphabetically.

Ideally the strings should really be broken up into constituent parts and ordered properly,
but occasionally you come across data where it's just not worth the while.

eg
select title from regulations order by title
returns:

Regulation 1(a) section 3
Regulation 11 section 100(b)
Regulation 11 section 2(c)(iii)
Regulation 2 section 1
Regulation 21 section 3 (b)

whereas
select title from regulations order by dbo.numeric_order(title,10)
returns:

Regulation 1(a) section 3
Regulation 2 section 1
Regulation 11 section 2(c)(iii)
Regulation 11 section 100(b)
Regulation 21 section 3 (b)

which is the order most users would expect.

NOTE: because the original strings are mixed, the user may include alphas between digits which are to be
sorted alphabetically, which means the string must keep all alpha parts of the original string as is.

expected output:
select dbo.numeric_order('Part 1(b) subsection 1',4)
returns
Part 0001(b) subsection 0001

test data:
use test
--go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[regulations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[regulations]
--go

create table regulations (id int identity(1,1), title varchar(200))
--go

insert into regulations (title) select 'Regulation 1(a) section 3'
insert into regulations (title) select 'Regulation 11 section 100(b)'
insert into regulations (title) select 'Regulation 11 section 2(c)(iii)'
insert into regulations (title) select 'Regulation 2 section 1'
insert into regulations (title) select 'Regulation 21 section 3 (b)'
--go

select title as [Incorrectly Ordered] from regulations order by title
--go

select title as [Correctly Ordered] from regulations order by dbo.numeric_order(title, 10)
--go

drop table [dbo].[regulations]
--go

*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[numeric_order]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[numeric_order]
go

create function [dbo].[numeric_order](
@numeric_string as varchar(1000),
@max_digits as int)
returns varchar(8000) as
begin
declare @return varchar(8000)

declare @part varchar(1000)
declare @digit_position int
declare @rest varchar(1000)
declare @non_digit_position int
declare @numeric_term varchar(1000)
declare @after varchar(1000)
declare @between varchar(1000)
declare @first_time int
declare @digits varchar(100)

--create a string of zeros equal in length to max number of digits of enclosed numeric values
set @digits = replace(space(@max_digits),' ','0')

--handle length issues
--worst case scenario is a number every second character multiplied by padlen < 8000
--which would potentially add len(@numeric_string)/2 * padlen characters - so subtract this from the string
set @part = left(@numeric_string, ((len(@numeric_string)/2) * @max_digits))

--starting values
set @non_digit_position = 0
set @first_time = 1
set @return = ''

--loop while not at end of string
while ((@non_digit_position > 0 or @first_time > 0) and (len(@part) > 0))
begin
--if there are digits in the string
set @digit_position = patindex('%[0-9]%', @part)
if @digit_position > 0
begin
--get the part of the string after the first digit
set @rest = substring(@part, patindex('%[0-9]%', @part) + 1, len(@part)-patindex('%[0-9]%', @part) + 1)
set @non_digit_position = patindex('%[^0-9]%',@rest)

--extract the string of digits
set @numeric_term = case
when @non_digit_position > 0 then substring(@part, @digit_position, @non_digit_position)
else substring(@part, @digit_position, len(@part) - @digit_position + 1)
end

--keep track of the rest of the string after and between the digits
set @after = substring(@part, @digit_position + len(@numeric_term), len(@part) - @digit_position + @non_digit_position)
set @between = '' + substring(@part, 1, @digit_position - 1)

--build return string
set @return = @return + @between + right(@digits + @numeric_term, @max_digits)
end
else
begin
--no more digits, just add back the rest of the original string
set @return = @return + @part
set @after = ''
end

--iterate
set @first_time = 0
set @part = @after
end

return @return

end
go

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Need To Split Numeric And Alphabetical Values From String

Jan 10, 2014

I need to split NUMERIC & ALPHABETICAL values from string.

for eg :-

1234heaven56-guy

output

123456 heaven-guy

View 3 Replies View Related

Numeric Strings Will Not Import From Excel

Sep 28, 2007

Hello,
I have a table in SQL Server 2005 that has an NVarchar(50) column called AcctCode. I am trying to populate this column from an Excel source. I am doing this from the Import/Export Wizrds in Management Studio. When I attempt the import from the Excel file, all the the AcctCode data that is alpha numeric or just alpha will import, but the all numeric data comes in as NULL.

If I make AcctCode NOT NULL, I get an integrity contraint error from the wizard. The rest of the records import. I've tried to make the column in the Excel file type 'text', and have tried it with no formatting on this column.

Why won't the all numeric Acctcodes import?

Thank you for your help!

cdun2

View 3 Replies View Related

Alphabetical Value Based Of Count?

Mar 6, 2014

there is another way to get the alphabetical value for a number other than using a case statement?

example:

Count = 1 That's = A
Count = 5 That's = E
Count = 8 That's = H

and so on I know a case could do this but wondering if there is a nice function or simple statement that would do this on the fly?

View 2 Replies View Related

Concatenate Strings After Assigning Text In Place Of Bit Strings

Feb 19, 2007

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:


select case new_accountant = 1 then 'acct/' end +

case new_advisor = 1 then 'adv/' end +

case new_attorney = 1 then 'atty/' end as String

from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

View 2 Replies View Related

T-SQL (SS2K8) :: Arithmetic Overflow Error Converting Numeric To Data Type Numeric

Jun 10, 2014

when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)

set @a=99362600999900.0000

The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.

View 2 Replies View Related

Arithmetic Overflow Error Converting Numeric To Data Type Numeric

Mar 21, 2006

Guys

I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000

When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)



Any ideas ??

View 6 Replies View Related

Sorting Column For Table - Not Alphabetical Or ID

Nov 26, 2007

Hi, I have a table whose Identifying column is not an integer but rather a manually entered id. (i.e. 106F, 106-09, G11 etc.) When sorted ascending, a G11 will come before a G2 in the list, 106-11 before 106-2, etc.I would like to insert a new column in the database or use some kind of function when sorting the database to ensure that the list in returned in the proper order. Any ideas?Much appreciated... 

View 16 Replies View Related

DB Design - Alphabetical Column Names?

Dec 13, 2004

I have been discussing with some coworkers whether or not it makes sense to invest the time to alphabetize the column names in our tables (aside from the PK and possibly FK's that could be listed first). My reasoning for doing so would make it much easier to scan the list of columns in a table that I was not familiar with to see if it contained a particular column (i.e. meeting_id). I was just wondering if this is common at all in our industry for new DB design (I realize why legacy systems would not be ordered in this way). I remember seeing MSFT designed their tables this way when Site Server first came out.

An argument was made that when you add a new column you would insert it into the appropriate location and in order to make this happen, EP needs to create a temp table, move the data and consequently lock the table until the data has been transferred. While this is correct, I do not see this negative as outweighing the positive experience achieved by scanning a list of fields in a table in a more orderly fashion.

Thoughts?

View 14 Replies View Related

Display Database Column In Alphabetical Order

Mar 12, 2007

Hello
I know how I can display a list of names in alphebetical order on my website:
Select L as [Last Name]
From  Name_CatEWhere Education = 'yes'Order ByLName ASC
However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.
What do I need to change in the following code, to view my database table column in a-z order?
 SELECT LName FROM Name_CatEORDER BY LName ASC
Thanks
Lynn

View 1 Replies View Related

SSIS Package Execution Results Alphabetical

May 1, 2008

When I veiw the exexcution/progress results for a package they show in order of the name of the task, is there anyway to see this tree view in order of the execution?

Thanks,

Casey Smith
MCT

View 1 Replies View Related

Removing Non-numeric Characters From Alpha-numeric String

Oct 24, 2007

Hi,

I have one column in which i have Alpha-numeric data like

COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.

I want to remove Non-numeric characters from above (space,'(',')',+,........)

i want to write something generic (suppose some function to which i pass the column)

thanks in advance,

Mandip

View 18 Replies View Related

TSQL Function To Return Numeric Value Of Non Numeric Field

Jul 20, 2006

I need to replace Access Val() functions with similiar function in sql.

i.e. Return 123 from the statement: SELECT functionname(123mls)

Return 4.56 from the satement: SELECT functionname(4.56tonnes)

Any one with ideas please

Thanks

George


View 1 Replies View Related

T-SQL (SS2K8) :: Select Only Rows With Alphabetical Character In Column?

Apr 17, 2014

i am working on a small project, that I have found that someone is storing a float as a varchar(). But there are also some actual words in the same column.

I am trying to determine how I can select only the rows with alphabetical characters in that column.

Some of the data is:

1.5008e+015
1.54453e+015
1.51922e+015
1.51922e+015
1.52243e+015

but there is a mix of alphabetical characters in there as well.

1.51922e+015
1.53122e+015
FMCIT
ABCNP
FMCPNG
1.62073e+015
1.6127e+015

I want to be able to select the rows with only the alphabetical characters. There is a huge mix, and I am assuming that every first letter is one of the 26 alphabetical character used. How can I write a query to use a REGEX to select any and all rows that cannot be CAST as a Float? I have nill to no experience using REGEX.

View 9 Replies View Related

Generate A Database Script In SQL 2005 In Alphabetical Order

Nov 7, 2007

In SQL 2000, when you generated an SQL script for a database, it was logical and the tables in the script were in alphabetical order. In SQL 2005 they are all mixed up. Am I missing something?

Thanks

Peter

View 8 Replies View Related

T-SQL (SS2K8) :: Unpivot Sorting Column In Alphabetical Order Automatically

Jan 27, 2015

I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,

Here is my code

@syear nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

[Code] ....

View 1 Replies View Related

SQL Server 2005 Database Generating Script DDL In Alphabetical Order

Jun 21, 2007

Using the scripting wizard in SQL Server 2005 database engine, I have been able to script all my DDL out to a flat file which is great; however, when I scripts for instance all views I would like to have the script in alphabetical order by view name, is there a value I can set to accomplish this?



Thanks

View 9 Replies View Related

Pattern Matching - Searching For Numeric Or Alpha Or Alpha-Numeric Characters In A String

Aug 18, 2006

Hi,

I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.





Wildcard
Meaning



%


Any string of zero or more characters.



_


Any single character.



[ ]


Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).






Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

using single quotes and the % wildcard instead of Access' double quotes and * wildcard.

Just putting this out there for anybody else that is new to SQL, like me.

Regards,

Patrick Briggs,
Pasadena, CA






View 1 Replies View Related

Ordering

Mar 3, 1999

Hi there!

We are experiencing a problem using order clause in the statement below:

SELECT
person.pkey
, first_name
FROM
person
, private_person
WHERE
fkey_person = person.pkey
AND first_name = 'A'
ORDER BY
first_name
, person.pkey

Where person table contains the following columns:

pkey, name etc

and private_person

pkey, fkey_person (indexed), first_name (indexed)

The output is:

STEP 1
The type of query is SELECT
FROM TABLE
private_person
Nested iteration
Index : i$private_person$first_name
FROM TABLE
person
Nested iteration
Using Clustered Index
pkey first_name
----------- ---------------------
2000512 A
10994 A
2299 A
1097 A
1218 A
5133 A
1329 A
1387 A
1465 A
7532 A
5513 A
1884 A
512 A
591 A

(14 row(s) affected)

STEP 1
The type of query is SETOFF

Why SQL server does not order by pkey? Is there any information about it somewhere? Is it a bug or what?

If we are ordering by fkey_person everything is Ok. Can anybody help?

Thanks a lot!

Kind Regards,
Vladimir

View 4 Replies View Related

Ordering Ids

Jun 18, 2007

I have a table named as C1_Messages with fields Id, Messages, Dates.
My Id filed looks like this:
1000
1001
1008
1009
1084
1093
1098 etc.

But here I need to make these Ids in a order, means it sould be in a consecutive order. Like
1000
1001
1002
1003
1004
1005 etc.

So is there anyway to do this method in SQL?

Regards
Shaji

View 5 Replies View Related

Ordering In The Table

Jun 27, 2006

Hello,
 
I have made sql table called costreallocation consists of two columsn:
RuleID varchar(10)
Type varchar(20)
I run the following three queries:
insert into costreallocation(RuleId,Type)values('aa','a')
insert into costreallocation(RuleId,Type)values('dd','d')
insert into costreallocation(RuleId,Type)values('cc','a')
then when i notice that these three records are not inserted as they are run:
In the table thn there are three records :
aa      a
cc      d
dd      d
but i need to be filled in the table as they run as:
aa      a
dd      d
cc      d
 
your help is highly appreciated
Best regards
 

View 2 Replies View Related

Ordering Dilemma

Jan 26, 2007

I have an int field - that will hold number for an ordering system. It's a databound field to a textbox in a grid, I want the default to be empty, however not all fields are required so i may have 1-6 as bound values, and the rest should fall in line behind them - the only thing i can think of to do is default the values to 9999 so they come after 1,2,3,4,5,6 etc... I don't like seeing 9999 in my bound fields.
thanks for any suggestions you may have.
 Jeff

View 1 Replies View Related

Advanced Ordering

Mar 5, 2001

Hello.
I want to order a resultset from a table by two columns in another table. Here's how it looks:

[Books]
ID
Title


[linkBookAuthor]
BookID (Books.ID)
AuthorID (Authors.ID)

[Authors]
ID
Firstname
Lastname
AuthorOrder

As you can see it's a many to many relationship. Is there a way to order the books by Authors.Lastname (the first if there are several authors) with one query ? If not, what is the fastest way ? I don't need the names in the resultset so I only want to order the books by Authors.Lastname, not include Authors.Lastname.

Thanks for any help.

View 1 Replies View Related

Ordering By @parameter Possible?

Mar 20, 2001

Hiya!
I've got a little stored procedure:
CREATE PROCEDURE rICDCode @param1 char(15) = 'Description'
AS
SELECT DiagCode, ICD9ID, Description
FROM ICDCodes
ORDER BY @param1
and SQL is returning error 1008 - "The SELECT item identified by the ORDER BY number %d contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."
I want to be able to use the same stored procedure for several different functions which all need the same rowset sorted differently. Any way to do this?

Thanks,
Sarah

View 2 Replies View Related

Custom Ordering Top 20 ?

Jul 11, 2005

Hi guys.

I want to create a top 20 product list from a few thousand products. I want the rest of the products to be grouped into 'others'...

I also want the products to be ordered by the facts in the cube. Thus the product dimension would dynamically change depending on the Time dimension thats being selected.

is this plausible ?

Thanks
Tom

View 1 Replies View Related

Ordering By One Field Or Another

May 16, 2008

Is it possible without using CASE statements, to order a result set by one field (if the second field return value is null) or another (if the second field return value is not null)

This would be on datetime fields.
Let's say I have 5 records with 2 date fields
sched_dt and arriv_dt.

sched_dt will always have a value, arriv_dt may or may not have a value.

In a single result set I would like to have the records with an arriv_dt sorted by arriv_dt and the ones without an arriv_dt sorted by sched_dt.

*Hope that makes sense*

Thanks

View 1 Replies View Related

Ordering Dates

Apr 7, 2005

I have a table that has 4 dates for each record (birthday, anniversary, policy_start & policy_end). What I want to do is cycle through each record and then do some calculations using the gaps between the dates. So it
might be Calc1 using (birthday->anniversary), Calc2(anniverary->policy_start), Calc3(policy_start->policy_end), etc. BUT the dates might be in different orders (ie the birthday could come first - or the anniverary could, etc).

In VBA in Excel, I can use the SMALL function within my calcs and this returns the minimum date, the next smallest, the next & then the biggest. But how can I do this in SQL. I guess I can push the 4 dates out to a temp table, order it and then suck them back in - but I have no idea how to do this for all the records in my primary table

I've also tried using various WHERE statements (ie do Calc1 using Birthday & Anniversary WHERE Birthday < Anniversary and ........ - but the code gets awfully long.

Is it possible to write my own SMALL function which uses a bubble sort on the 4 dates & then returns the smallest, next smallest, etc

Any thoughts ?

View 2 Replies View Related

Re-ordering Records

Jul 5, 2007

Hi all,

Ive got a table with a field called 'morder' which orders a menu based on the values in this field (1 for position 1, 2 for position 2 etc...) for example

1 - menu item 1
2 - menu item 2
3 - menu item 3
4 - menu item 4

If I want to add a record to this table and put it at number 2 in the list, i need to update the table to then read...

1 - menu item 1
2 - NEW menu item 2
3 - menu item 2
4 - menu item 3
5 - menu item 4

I want to use a mssql or php function to re-order this field... is it possible??

Hope you can help, and hope it makes sense...

Ash

View 2 Replies View Related

Specific Ordering

Jul 12, 2004

Hi, I was wondering, I have several columns of data that are able to be sorted on - the user just clicks on the column name. However, we have 1 column called order status where we don't want the order to be alphabetical (and it is a text field - a couple current possible values are Processing...In Production...Waiting For Approval). This order is not good for logical sorting - we would want to be able to specify what value would get order. Does that make sense? So we would want to be able to say that Processing is first in the display, Waiting For Approval would be next, then In Production would be next, and so on. Is there any possible way of doing this in a SQL query? Or am I going to have to manually modify all the data adding numbers to the beginning of each data so that it will sort on the numbers? Thanks everyone.

View 4 Replies View Related

About Ordering Of Columns

Jan 2, 2007

i add new column using alter command but i always found it in the end of table but
i want to add it in particular position between the columns...........

how to do so .............:)

View 6 Replies View Related

Ordering By Date

Jun 7, 2007

Hi,I'm streaming data monitoring histories of components into a database table with the following three columns: Hour (DateTime), Id (Int64), and Value (Int32). The Value entry is an aggregate of all values sent from component Id during, and 59 minutes and 59 seconds after, the time listed in the Hour column.I had rather not have to sort the queries after pulling them from the database by date. So I tried to index the DB by the Hour column. Any column will inevitably have duplicates, since the uniqueness depends on a combination of Hour and PortId. But Indexing the Hour column doesn't result in INSERTs being in order as I had expected. Instead, every entry is listed in order of insertion.So. . .how can I keep such a table ordered by date on the disk? I'm afraid this will become very inefficient if this isn't nipped in the bud right now.Thanks so much for your help!-Brandon

View 4 Replies View Related

Ordering Days

May 19, 2004

I have a little query that returns me all the days in a month, but the days are not in the order I need. They come out like so..

January 10
January 11
January 12
January 13
January 14
etc
January 19
January 2
January 20
January 21
January 22

here is my sample code

select [month] + ' ' + [day] as [Date] from mytable
where [month] = 'january'
and [year] = '2004'

Thanks, Jeff

View 10 Replies View Related

Ordering By Months

Jun 9, 2004

I am wondering if someone maybe able to help me, I am needing to order my data via months in the calendar sense not alphabetically, below is what I currently have, but it only does it alphabetically.

select to_char(created,'yyyy-Mon'), matdesc, count(*)
from test
group by to_char(created,'yyyy-Mon'), matdesc
order by to_char(created,'yyyy-Mon') desc

any help would be greatly appreciated

Thank you

Stephen

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved