Convert to Number IF it is a number

Pardon my newbieness:
I have a column that could contain numbers or text (not my idea). All
of the values are returned as text. I want to return the value as a
number if it is a number, otherwise (of course) leave it as text. What
can I do?Return the value to where?
A single column in a resultset has exactly one datatype.
Can't your receiving application or front end code figure out what kind of
data it is?
Why are you storing numbers and strings in the same column?
A
"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>|||> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
Use the ISNUMERIC function. Check the syntax in Books OnLine.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>
A column only has a single datatype so I'm going to assume you require a
result set with two columns: one VARCHAR, one INTEGER. I will also assume
that when you say "text" you really mean "VARCHAR". Doing this using the
TEXT datatype would be a bit more tricky. That's too many assumptions but if
you don't post a more precise spec then assumptions and guesses are what you
will get. You can help us to help you better by taking the advice in the
following article:
http://www.aspfaq.com/etiquette.asp?id=5006
Here's my example:
CREATE TABLE tbl (x VARCHAR(10) PRIMARY KEY) ;
INSERT INTO tbl (x)
SELECT '123' UNION ALL
SELECT '99A' UNION ALL
SELECT 'ABC' ;
SELECT x, CAST(num AS INTEGER) AS num
FROM
(SELECT x,
CASE WHEN x NOT LIKE '%[^0-9]%' THEN x END AS num
FROM tbl) AS T ;
Result:
x num
-- --
123 123
99A NULL
ABC NULL
(3 row(s) affected)
Hope this helps.
David Portas
SQL Server MVP
--|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23R%23NQQKEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> Use the ISNUMERIC function. Check the syntax in Books OnLine.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>
Before using ISNUMERIC Tod should be aware of its limitations. Read:
http://www.aspfaq.com/show.asp?id=2390
David Portas
SQL Server MVP
--|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.

convert sql selects into xml format

hai all,

I need some help and is very urget. I wan to convert the output of the sql query into xml format but i don hv any idea on how to do it. I'm using vb.net for this application.

Hope your guys out there can help me on this.

Regards,

KYY

Read up on the FOR XML clause that SQL Server supports: http://msdn2.microsoft.com/en-us/library/ms191268.aspx

Convert Informix Stored Procedure

I would like to convert a couple informix stored procedures to SQL
Server stored procedures. I have no idea how to accomplish this. Here
is an example of one of the procedures I need to convert.

drop function mnaf_calc_calendar_quarter;

CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
--************************************************** ***************************
-- Name: mnaf_calc_calendar_quarter
-- Description/Notes:
-- Calculates the most recent calendar quarter based on the end date.
--
-- Parms:
-- End Date.
-- Returns:
-- The calculated period start date and end date.
--
--************************************************** ***************************
-- Revisions:
-- PgmrDate # Description
-- HPI05/03/2005
--************************************************** ***************************

RETURNING date, date;

DEFINE dtStartDate date;
DEFINE dtEndDate date;

LET dtStartDate = mdy(12,31,1899);
LET dtEndDate = pEndDate;

-- If the end date parameter is equal to a calendar quarter,
-- calculate the start date by subtracting three months.
IF month(pEndDate) = 3 or month(pEndDate) = 6 or
month(pEndDate) = 9 or month(pEndDate) = 12 then

LET dtEndDate = pEndDate;

ELSE

-- Otherwise find the closest previous calendar quarter end date
-- then calculate the start date.
IF month(pEndDate) = 1 or month(pEndDate) = 4 or
month(pEndDate) = 7 or month(pEndDate) = 10 then

-- Subtract 1 month off end date parameter to get the calendar
qtr end date
LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 units
month);

ELSE

-- Month must be equal to 2, 5, 8, 11
-- Subtract 2 months off end date parameter to get the calendar
qtr end date
LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 units
month);

END IF;

END IF;

-- Calcuate the start date by subtracting off two months
LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);

RETURN dtStartDate, dtEndDate;

END FUNCTION;

grant execute on mnaf_calc_calendar_quarter to public;"Matt" <matt_marshall@.manning-napier.com> wrote in message
news:1118858793.011839.15120@.g49g2000cwa.googlegro ups.com...
>I would like to convert a couple informix stored procedures to SQL
> Server stored procedures. I have no idea how to accomplish this. Here
> is an example of one of the procedures I need to convert.
> drop function mnaf_calc_calendar_quarter;
> CREATE FUNCTION mnaf_calc_calendar_quarter(pEndDate Date)
> --************************************************** ***************************
> -- Name: mnaf_calc_calendar_quarter
> -- Description/Notes:
> -- Calculates the most recent calendar quarter based on the end date.
> --
> -- Parms:
> -- End Date.
> -- Returns:
> -- The calculated period start date and end date.
> --
> --************************************************** ***************************
> -- Revisions:
> -- Pgmr Date # Description
> -- HPI 05/03/2005
> --************************************************** ***************************
> RETURNING date, date;
> DEFINE dtStartDate date;
> DEFINE dtEndDate date;
> LET dtStartDate = mdy(12,31,1899);
> LET dtEndDate = pEndDate;
> -- If the end date parameter is equal to a calendar quarter,
> -- calculate the start date by subtracting three months.
> IF month(pEndDate) = 3 or month(pEndDate) = 6 or
> month(pEndDate) = 9 or month(pEndDate) = 12 then
> LET dtEndDate = pEndDate;
> ELSE
> -- Otherwise find the closest previous calendar quarter end date
> -- then calculate the start date.
> IF month(pEndDate) = 1 or month(pEndDate) = 4 or
> month(pEndDate) = 7 or month(pEndDate) = 10 then
> -- Subtract 1 month off end date parameter to get the calendar
> qtr end date
> LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 1 units
> month);
> ELSE
> -- Month must be equal to 2, 5, 8, 11
> -- Subtract 2 months off end date parameter to get the calendar
> qtr end date
> LET dtEndDate = mnaf_eomonth(mnaf_bomonth(dtEndDate) - 2 units
> month);
> END IF;
> END IF;
> -- Calcuate the start date by subtracting off two months
> LET dtStartDate = (mnaf_bomonth(dtEndDate) - 2 units month);
> RETURN dtStartDate, dtEndDate;
> END FUNCTION;
> grant execute on mnaf_calc_calendar_quarter to public;

See "Date and Time Functions" in Books Online - DATEPART() and DATEADD()
will probably be the ones you're looking for. This article might also be
useful for general background information about manipulating datetime data:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon