Ordering Varchar Column Numerically

Jul 23, 2005

Is there a preferred way to order a varchar column numerically (for those
that are numeric), then alphanumerically for all others?

I've tried:

ORDER BY CASE WHEN IsNumeric(<column_name>) = 1 THEN CONVERT(Float,
<column_name>) ELSE 999999999 END;

and

ORDER BY CASE WHEN IsNumeric(<column_name>) = 1 THEN 0 ELSE 1 END, CASE WHEN
IsNumeric(<column_name>) = 1 THEN CONVERT(Float, <column_name>) ELSE 999999
END, <column_name>;

Neither of these however give the desired results.

Any ideas?

Thanks,
Frank

View 4 Replies


ADVERTISEMENT

Sorting Numerically, And Alpha Numerically

Jan 17, 2007

Hi,

I'm hoping there is an easy way to deal with this....

I have a UNION query. The first SELECT in the UNION I want sorted by an int, the the second SELECT in the union (same column) I want sorted by a char.

Here's a really basic sample of what I mean:

select dummy_data.test from
(select cast(1 as char) as test
union
select cast(11 as char) as test
union
select cast(2 as char) as test
union
select 'Alpha' as test
union
select 'Bravo' as test) dummy_data
order by test asc

..obviously, I need to cast the INT columns as chars or the UNIONs do not work.

However, the sort will sort the numbers alpha numerically: 1,11,2
(I'm looking for 1,2,11)

Any thoughts?

david.

View 4 Replies View Related

Sort Numerically From An Alphanumeric Column

Aug 9, 2006

Hi,

I need to get Sort numerically from an alphanumeric column

Regards,
Kihsore

View 3 Replies View Related

Ordering An Int Column

Sep 26, 2005

I've created a clustered two column index on a table where the secondcolumn is an integer value. When the first column is the same, insteadof ordering in numerical order it is ordering 1,10,100,2,20,200 etc.How can I change the behaviour to order the data in numerical order?Thanks,Rick

View 9 Replies View Related

Problems Moving Data Over 8000k In DB2 Varchar Column Into SQL Server Varchar(max) Using SSIS

Nov 20, 2007



I have looked far and wide and have not found anything that works to allow me to resolve this issue.

I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.

I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".

[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".

[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?

View 10 Replies View Related

WHERE [column] IN (list) - Question About Ordering

Oct 12, 2005

Hey all,
When you use a WHERE/IN statement, is there a way to make sure the results are ordered in the same order you specified in the IN list?

View 2 Replies View Related

Ordering A Column In Descending With Numbers First

Oct 8, 2013

I have a Column which has both numeric values and Alphabets in them.I want to sort them in descending with numbers first and then Alphabets later.

Example:

Source column:
2008
2005
2010
2013
All-Year
Month-year

Required Output:

Source Column:
2013
2010
2008
2005
Month-Year
All-year

View 4 Replies View Related

Ordering Data By Entry Rather Than Column

Apr 16, 2008

In my search results I need to order my data by a particular entry rather than a particular column and then add all the other entries onto the end of the result set. Specifically I'm trying to order my records by a particular location in a city so those entries in that location appear first in the result set and then on the end I want to include all the other locations in that city.

I'm assuming I'll need to do these as separate queries and then use a UNION statement but I'm wondering if there is a better or easier way. Is there, for example, another type of ORDER BY clause that lets me order by a particular entry rather than a column?

Thanks

View 12 Replies View Related

How To Use Varchar With Int Column

Jul 23, 2005

Hi,I am writing a SP in MSSQL. One of the parameted is VARCHAR typewhich have comma seperated INT vlaues. I want to use this varible inWHERE clause against INT type coloum. how do I achive this.eg. DECLARE @CompanyTypeIDs VARCHAR(200)SET @CompanyTypeIDs = '1,3,2'Currently I am using it asSELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN (1,2,3)CompanyTypeID is of INT type.But I want to replace the consts with the Parameter passed as belowSELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN(@CompanyTypeIDs) - but this is not giving any result.Please let me know the solution.Many Thanks,Mahesh

View 10 Replies View Related

Varchar Column Check

Sep 21, 2004

Can't seem to get my head around this: I'm looking for a way to select only those varchar(10) values that soley consist of numbers. Leading spaces are allowed.

I tried using isnumeric, but it also allows those with periods, comma's etc.
Also tried using like, but the length of the varchar column varies too much to do a like '[0-9][0-9]....'.

As a solution I currently do a combo of isnumeric, not like '%.%', not like '%,%' etc. I need to do a conversion to an int to join another table, but the convert still fails. Not sure where and why.

I'm thinking there should be a better way than create a hughe list of "not like " but it looks like I'm in the woods here...

View 2 Replies View Related

Parsing A Varchar(79) Column

May 18, 2006

Hi,I have a varchar column with values like:000 BNP=Item one HOP=Item two LLT=Item three001 LLT=Item one002 BNP=Item one LLT=Item twoI want to parse this rows into a table similar to the following output:000 BNP=Item one000 HOP=Item two000 LLT=ItemThree001 LLT=Item one002 BNP=Item one002 LLT=Item twoHas anybody done a similar assignment? Can you share me your solution?Thank you much!Edgar J.

View 4 Replies View Related

Index On A Varchar Column?

Jul 20, 2005

In an effort to improve the speed of queries against my main table,I'll be indexing a column whose data type is varchar(50).Would I be better off (better performance) if I changed the column'sdata type to some numeric type? I would have to update the column'sdata to accomodate this, but I would do it if this offers aperformance gain.-- Bill

View 6 Replies View Related

Output Varchar Column

May 3, 2006

Hi,

I am dumping varchar(40) column from a OLE DB Source to Excel file but it is failing due this error

[Excel Destination [239]] Error: Column "ItemDescription" cannot convert between unicode and non-unicode string data types.

How can I resolve this issue?

Thanks

View 8 Replies View Related

Hyperlink In Varchar Column Sql Server2005

Aug 20, 2007

Hi Folks,
Is it possible to add <a href=""http://www.yahoo.com"">xxxxxx</a> tag in varchar column as a text and display it as html hyperlink xxxxx (hyperlink).
 Thanks in advance
 Regards

View 25 Replies View Related

How To Cast A Varchar Column To DateTime

Jan 21, 2012

Below is my query.Its working great if i remove ,Cast(C.ClassTime as time) as StartDate.But when i use this i get an error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value My ClassName is a varchar.Whose definition i cant change to DateTime now.But i want to cast it to DateTime.

Code:
Select C.ClassID as Appointment_Id,C.ClassName as Appoitment_Descr,Cast(C.ClassTime as time) as StartDate,C.EndClassTime as EndDate, 'Class' as Type
From Dojo D inner join DojoClass C on D.SchoolID = C.DojoSchoolID
Where D.SchoolID = @DojoID
and C.Days like'%' + @Days + '%'
Union
Select E.DojoEventID as Appointment_Id,E.EventName as Appoitment_Descr , E.EventStartDate as StartDate , E.EventEndDate as EndDate,'Event' as Type
From Dojo D inner join DojoEvent E on E.DojoID = D.SchoolID
Where D.SchoolID = @DojoID and @Date
Between E.EventStartDate and E.EventEndDate

how can i cast it correctly?

View 1 Replies View Related

T-SQL (SS2K8) :: How To Return Max From A Varchar Column

Jan 21, 2015

I need to return the max value from a fieldwhich contains a three part numeric, stored as a varchar. For example

1.0.0
1.0.1
1.1.0
1.2.1
2.0.0
2.1.1
etc

These represent processes, and sub tasks. So I want to return the highest process and its highest task and sub task.

View 9 Replies View Related

T-SQL (SS2K8) :: Distinct On Varchar Column

May 21, 2015

The below TSQL query takes about 19 minutes to run

SELECT count(distinct SessionID) ) FROM SessionTracker

Sessionid is a varchar(138)

Through SQL profiler - I notice implicit conversion taking place when running distinct option.

Select count(sessionid) from sessiontracker

- runs in milliseconds.

View 1 Replies View Related

Best Way To Force A Varchar Column To Have No Whitespace

Oct 2, 2006

I have a column that I do not want any whitespace in whatsoever. I'mwondering how do enforce this a DDL level instead of in 40 millionseat-of-the-pants after-the-fact computer programs accessing thedatabase.Regards,Terrence

View 5 Replies View Related

Query By Length Of Varchar Column

Sep 14, 2007

Hi mates,

I have a Table:Test with column text:varchar(255). I want get rows where text length to be longer than 100. Is it possible?

Thx in advance,

View 3 Replies View Related

SQL Security :: Encryption On Varchar Column?

Nov 26, 2015

Is there any way i could apply encryption on a varchar type column without changing its datatype to varbinary.

View 6 Replies View Related

Transform One Varchar Column Into Many Bit Columns

May 19, 2007

Hi all,
I'm new at this SSIS but have been able to successfully create some simple packages. My situation is that at work we use a column to describe a status of applications. However, this makes for hellacious query because some of those statuses inherintly were one or more statuses previously. Example
Admit = Admit
Accept = Admit then Accept
Withdraw Accept = Admit, Accept, then Withdraw
Decline = Admit then Decline
As you can see inherintly those were all admits at one point. So what I'd like to do is instead of having long queries for example to get all my "Admits", I'd rather query another table that has the following columns as bits:
Admit
Accept
Withdraw
That way I can query the admit column and get all my admits. How can I use SSIS to transform my "Decision" column into those bit columns?
Thanks for any help or suggestions you have.

View 13 Replies View Related

How To Put Multiple Lines In A Varchar(MAX) Column?

Apr 24, 2007

how to put multiple lines in a varchar(MAX) column?



when I cut&paste it only pastes up to the first newline

CTRL/ENTER does not work (like it does in an Access memo column for example)



there must be a way to put multiple lines



help will be appreciated

View 6 Replies View Related

How To Convert A Column Datatype From Varchar To Varbinary?

Feb 3, 2007

I have a password column that needs to be converted from varchar to varbinary. Can anybody provide me a proper CONVERT statement syntax for it?

View 12 Replies View Related

Trim Leading Zeros From A Varchar Column?

Aug 10, 2007

 My table has a column named [Liability] varchar datatype which has the data in the format
(
3535.00,
00393.99,
00Loan,
0.00,
.00
*.00
)
 
I want to trim the leading zeros so that the output should be(trim only the leading zeros)
(
3535.00,
393.99,
Loan,
0.00,
.00
*.00
)
 
Can someone show my the sql statement for this?
 
Thanks.

View 11 Replies View Related

Replace Carriage Return In Varchar Column

Jul 31, 2007

Hi

How do i remove Carriage return in a varchar column?

Thanks

View 4 Replies View Related

Decreasing The Varchar Column Sizes In A Table

Jul 27, 2004

I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much....Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.

Thanks.

View 2 Replies View Related

SQL Server 2008 :: Get XML Text From Varchar Column

Jan 30, 2015

create table tblxmldata
(id int, xmltext varchar(max))
insert into tblxmldata values(1,'<associatedText><value type="PO">GTT taken</value></associatedText>')
insert into tblxmldata values(1,'<associatedText><value type="PO">Check sugar today please</value></associatedText>')

I want the output as

GTT taken
Check sugar today

View 9 Replies View Related

T-SQL (SS2K8) :: Group Sum LEN On VARCHAR Column And Concatenate

Jul 3, 2015

I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?

View 1 Replies View Related

Retrieve Part Of Address - Column Is Varchar?

Mar 25, 2015

I want to just retrieve the numbers left of the space in this address column what can I use? The Address column is Varchar.

Address:
6501 Red Hook Place #201
32 Orchard St
1 Hardy Rd ste 210
379 E Center St.
170 Bridge St
399 Interpace Pkwy
1800 Valley Rd
2210 Harding Hwy
41 Maple Ct

Expected Results:

Address
6501
32
1
379
170
399
1800
2210
41

I tried using this:

Left([ADDRESS], Charindex(' ',[ADDRESS] - 1)) as 'Address'

But i received an error:

Conversion failed when converting the nvarchar value '6501 Red Hook Place #201' to data type int.

View 2 Replies View Related

Removing White Spaces In A Varchar Column

Sep 15, 2005

I have a table . It has a nullable column called AccountNumber, whichis of varchar type. The AccountNumber is alpha-numeric. I want to takedata from this table and process it for my application. Before doingthat I would like to filter out duplicate AccountNumbers. I get most ofthe duplicates filtered out by using this query:select * from customerswhere AccountNumber NOT IN (select AccountNumber from customers whereAccountNumber <> '' group by AccountNumber having count(AccountNumber)[color=blue]> 1)[/color]But there are few duplicate entries where the actual AccountNumber issame, but there is a trailing space in first one, and hence thisduplicate records are not getting filtered out. e.g"abc123<white-space>" and "abc123" are considered two different entriesby above query.I ran a query like :update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)But even after this query, the trailing space remains, and I am notable to filter out those entries.Am I missing anything here? Can somebody help me in making sure Ifilter out all duplicate entries ?Thanks,Rad

View 3 Replies View Related

Inserting Varchar Values Which Is Int Value In Int Column In Server

Aug 6, 2015

IF I have a table like the below one and i have to insert a number value which is inserted as varchar in an int columnĀ  then what is expected behavior of this statements .

create
table stud
(id
int)
insert
into stud
values ('1').

I thought it should fail but it succeeds...

View 4 Replies View Related

How To Trim Leading Zeros From A Varchar Column?

Aug 10, 2007

My table has a column named [Liability] varchar datatype which has the data in the format
(
3535.00,
00393.99,
00Loan,
0.00,
.00
*.00
)

I want to trim the leading zeros so that the output should be(trim only the leading zeros)
(
3535.00,
393.99,
Loan,
0.00,
.00
*.00
)


Can someone show my the sql statement for this?

Thanks.

View 4 Replies View Related

Searching For Dates In A Column Of Type Varchar

Feb 22, 2008



I have a DB named zCIFRecord with a column named CIFUpdateDate which is of datatype varchar. The data is a date MM/DD/YYYY 01/30/2008, this is al that is in this column. I can search this colum for individual dates and for a range of dates. My problem is with a range of dates that is not within the same year, such as;


SELECT [CIFPan]

,[CIFMemNum]

,[CIFLName]

,[CIFFName]

FROM [FutureSoft].[dbo].[zCIFRecord]

WHERE [CIFUpdateDate] between '12/01/2007' and '01/30/2008'


will return nothing because it seems to only search on the 12 then the 01 then the 2008. this search can be performed properly on dates within the same year such as;

SELECT [CIFPan]

,[CIFMemNum]

,[CIFLName]

,[CIFFName]

FROM [FutureSoft].[dbo].[zCIFRecord]

WHERE [CIFUpdateDate] between '01/01/2008' and '01/30/2008'

will return the proper values because now all the numbers are in correct order. How can i create a search that lets me perform the first query as well as the second query. I tried to convert to a float but you cant convert a varchar to a float.

View 17 Replies View Related







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