Consolidation Query: varchar

Showing posts with label varchar. Show all posts

Thursday, March 29, 2012

Convert AlphaNumeric to Numeric

Hello,

I have to convert a alpha numeric value to a numeric value using query.
Is there a way to do it.

The column data type is Varchar and I am storing alpha numeric values to it.
I have to sort the column now. when I say order by [column name] it is not comming properly.

Need some solution to do it.

Regards,
GowriShankar.

Quote:

Originally Posted by gowrishankar

Hello,

I have to convert a alpha numeric value to a numeric value using query.
Is there a way to do it.

The column data type is Varchar and I am storing alpha numeric values to it.
I have to sort the column now. when I say order by [column name] it is not comming properly.

Need some solution to do it.

Regards,
GowriShankar.

What do you mean by "alphanumeric value"? Do you have non-numeric chars in the column? If yes, how do you expect it to be converted to numeric? If no, then use "convert(int, ColumnName)" or "cast(ColumnName as int)" statements.
Please post examples|||

Quote:

Originally Posted by almaz

try like this
SELECT Description
FROM ModuleSetup
ORDER BY CAST(Description AS varchar)

Tuesday, March 27, 2012

convert a datatype

i have table tt.
it contains two fields one is ttno int,doj datetime . i want to
convert to datetime to varchar .
how it is ... give me some examplesNo problem, look at the CONVERT function for a specific format you want
to extract. I prefer using the ISO one, though its good for ordering
and international.

CONVERT(VARCHAR(10),GETDATE(),112)

HTH, jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Check the topic CONVERT in SQL Server Books Online.

--
Anith|||Anith Sen (anith@.bizdatasolutions.com) writes:
> Check the topic CONVERT in SQL Server Books Online.

Actually, the topic is CAST and CONVERT, lest someone looks in the wrong
place and don't find it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Convert $ to varchar decimal problem

I'm trying to convert a check amount to a fixed length string with
leading zeros and no decimal point. All is well, except for the pesky
decimal point. Here is what I have:
COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
(CONVERT(varchar(12),ckamt))
Thanks for any ideas on how to accomplish this.What exactly do you need? Could you give an example of the expected result?
E.g. I have 3.04, I want 003...
ML
http://milambda.blogspot.com/|||Multiply the number by 100 (or which ever multiple of 10 will remove the
decimal place), turn that number into an integer and then convert it into a
string so that 3.04 becomes 304.00 becomes 304 becomes 000304.
Ta,
M. E. Houston
<birdbyte@.gmail.com> wrote in message
news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||Take a look at this
declare @.d decimal(12,2)
select @.d =3.04
select @.d, right('000000000000' +
convert(varchar,replace(@.d,'.','')),12)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
birdbyte@.gmail.com wrote:
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.|||Try,
declare @.m money
declare @.i int
set @.m = 12345.54
set @.i = 12
select replace(str(round(@.m, 0, 1), @.i, 0), ' ', '0')
go
AMB
"birdbyte@.gmail.com" wrote:

> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||Replicate to 13, and then REPLACE({your stuff below}, '.', '')
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<birdbyte@.gmail.com> wrote in message
news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
> I'm trying to convert a check amount to a fixed length string with
> leading zeros and no decimal point. All is well, except for the pesky
> decimal point. Here is what I have:
> COALESCE(REPLICATE('0', 12-LEN(CONVERT(varchar(12),ckamt))),'') +
> (CONVERT(varchar(12),ckamt))
> Thanks for any ideas on how to accomplish this.
>|||I don't think this this idea is quite right. If you REPLACE() the decimal in
the decimal value, it will round.
I think you need to REPLACE() the decimal after it is converted to a
varchar().
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1151514826.136274.129340@.x69g2000cwx.googlegroups.com...
> Take a look at this
> declare @.d decimal(12,2)
> select @.d =3.04
> select @.d, right('000000000000' +
> convert(varchar,replace(@.d,'.','')),12)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> birdbyte@.gmail.com wrote:
>|||Or convert the decimal to an int then convert to varchar. These two
methods are assuming you want to always round down.
On Wed, 28 Jun 2006 11:52:35 -0500, "M. E. Houston"
<m.e.houston@.gmail.com> wrote:

>Multiply the number by 100 (or which ever multiple of 10 will remove the
>decimal place), turn that number into an integer and then convert it into a
>string so that 3.04 becomes 304.00 becomes 304 becomes 000304.
>Ta,
>M. E. Houston
><birdbyte@.gmail.com> wrote in message
>news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...
>|||Great suggestion. Thanks.
Arnie Rowland wrote:
> Replicate to 13, and then REPLACE({your stuff below}, '.', '')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <birdbyte@.gmail.com> wrote in message
> news:1151512320.378853.160030@.75g2000cwc.googlegroups.com...

convert

hi,
How do I convert this into smalldatetime please?
I am doing this because there is a field of type varchar which has to go into a separate table with field of type smalldatetime.

select convert(smalldatetime, '14/10/04', 101)

This is what I have but the error is:
Conversion failed when converting character string to smalldatetime data type.

This looks like a locale issue; try executing a SET DATEFORMAT DMY before executing your convert. You might also want to give a look to Umachandar's comments in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=597495&SiteID=1

Note the suggestion to try to use one of the ISO formats when possible.

Code Snippet

set dateformat dmy

select convert(varchar, cast('14/10/7' as datetime), 101) [a date/time]

/*
a date/time
10/14/2007
*/

select cast('14/10/7' as smalldatetime) as [converted]

/*
converted
2007-10-14 00:00:00
*/

|||

Try this:

select convert(smalldatetime, '10/14/2004', 101)

You already had smalldatetime and the system was probably expecting the MM/DD/YYYY format rather than DD/MM/YYYY

sql sql

Convert

Hi All,
Sample:-
Declare @.xx varchar(10)
select @.xx = '1x'
select convert(int, @.xx) && Error
а?i_ΥX error , H_P System .
] @.xx @., ?Twi Convert.
Thanks !Hi
What are you trying to do? 1x is not an integer value!
You may want to look at the undocumented procedure xp_varbintohexstr to
convert a varbinary value to a hexadecimal string:
declare @.hexstr varchar(100)
declare @.bin varbinary(10)
set @.bin = 0xAA
set @.hexstr = ''
exec master.dbo.xp_varbintohexstr @.bin, @.hexstr output
print @.hexstr
-- Or use cast/convert to change back
select convert(int, @.bin), CAST(@.bin as int )
John
"SOHO" wrote:

> Hi All,
> Sample:-
> Declare @.xx varchar(10)
> select @.xx = '1x'
> select convert(int, @.xx) && Error
> ?D°Y￥i§_¤￡￥?￥X error , ￥H_P System °±¤?.
> |]?° @.xx ￥??°¤@.??|ì, ?ê??¤￡?T?w￥i Convert.
>
> --
> Thanks !
>
>|||Hi John Bell,
Thanks for your reply.
а?pib SQL Server Online Books ?o_ function
"master.dbo.xp_varbintohexstr" ?k,
Χ??_?Oiw, iO "1x+&2" or "abcxyz", thanks.
Thanks !
"John Bell" <jbellnewsposts@.hotmail.com> glsD:7921B130-EB91-49BC-B88E-1C3E06A55A3
9@.microsoft.com...
> Hi
> What are you trying to do? 1x is not an integer value!
> You may want to look at the undocumented procedure xp_varbintohexstr to
> convert a varbinary value to a hexadecimal string:
> declare @.hexstr varchar(100)
> declare @.bin varbinary(10)
> set @.bin = 0xAA
> set @.hexstr = ''
> exec master.dbo.xp_varbintohexstr @.bin, @.hexstr output
> print @.hexstr
> -- Or use cast/convert to change back
> select convert(int, @.bin), CAST(@.bin as int )
> John
> "SOHO" wrote:
>|||Hi
Unfortunately the your character set is not being displayed correctly, so I
am not sure if you are wanting a reply!
John
"SOHO" wrote:

> Hi John Bell,
> Thanks for your reply.
> ?D°Y|p|ó￥i|b SQL Server Online Books ?Y¨ì3o_ó function
> "master.dbo.xp_varbintohexstr" ao￥?ak,
> ¤?§úao?ü??_è?O¤￡￥i1w′
ao, ￥iˉ_?O "1x+&2" or "abcx
yz", thanks.
>
>
> --
> Thanks !
>
> "John Bell" <jbellnewsposts@.hotmail.com> ???g?ó?l￥ó·s?D:7921B130
-EB91-49BC-B88E-1C3E06A55A39@.microsoft.com...
>
>|||John Bell skrev:

> Hi
> Unfortunately the your character set is not being displayed correctly, so=
I
> am not sure if you are wanting a reply!
> John
>
Huh? What part of " =A4=CE=A7=DA=AA=BA=C5=DC=BC=C6=AD=C8=ACO
=A4=A3=A5i=B9w=
=B4=C1=AA=BA,
=A5i=AF=E0=AC"
did you not understand ;)
/impslayer, also

|||I am easily

!
I hope that was not rude!!!
John :)
"impslayer" wrote:

> John Bell skrev:
>
> Huh? What part of " ¤?§úao?ü??_è?O¤￡￥i1w′
ao,
> ￥iˉ_?"
> did you not understand ;)
> /impslayer, also

convert

can someone give me the command to conver varchar to date please.
In my table I have varchar(50) Tue Nov 21 00:00:06 EST 2006 for the column
but need to update the colum with 2006-11-21 00:00:06.
Thanks,
Stoneystoney wrote:
> can someone give me the command to conver varchar to date please.
> In my table I have varchar(50) Tue Nov 21 00:00:06 EST 2006 for the column
> but need to update the colum with 2006-11-21 00:00:06.
> Thanks,
> Stoney
Try to look up the CONVERT command in BOL. Here you can see the various
styles you can use. I think in your case you can use 20/120.
Regards
Steen Schlüter Persson
Database Administrator / System Administratorsql sql

conversion to date

HI everyne,

I have a varchar field in one table, which contains data in the form '010706' and I want to convert this to date datatype to 01/07/2006 (Jan 07, 2006). When I just import the data to the other table it gets converted to 7/6/2001, how can I convert it right? Please help.

CAST(RIGHT(datafield,2)+LEFT(datafield,4) AS datetime)

|||Thanks Motley. It works!

Sunday, March 25, 2012

Conversion of Varchar into float

We convert a varchar column into float so that the data is ordered logically
like (1,2,10,11) instead of (1,10,11,2).
We have noticed one peculiar issue. When this query runs for a specific
range of inputs for the float values, it selects records which are outide
the inputs ranges
For example, if the query is run for float values between 1 and 10 then
values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
We tried using the float conversion part of the where clause error, but it
gives a data type conversion error . Is there any other method to order the
varchar values logically besides converting into float.
Need forum members help on this
Soura.Could you perhaps post the query so that we know what steps you are
taking to accomplish your goal?|||Hi
Check out http://www.sommarskog.se/arrays-in-sql.html to convert it into an
orderable format, you would then need to reconstitute the string.
John
"SouRa" wrote:
> We convert a varchar column into float so that the data is ordered logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
> We tried using the float conversion part of the where clause error, but it
> gives a data type conversion error . Is there any other method to order the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||Do your numbers have decimals? If not, then you should be using int and not
float.
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> We convert a varchar column into float so that the data is ordered
> logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> ?
> We tried using the float conversion part of the where clause error, but
> it
> gives a data type conversion error . Is there any other method to order
> the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||We do have decmials .I forgot to mention about this in my original post
"Michael D'Angelo" wrote:
> Do your numbers have decimals? If not, then you should be using int and not
> float.
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> > We convert a varchar column into float so that the data is ordered
> > logically
> > like (1,2,10,11) instead of (1,10,11,2).
> >
> > We have noticed one peculiar issue. When this query runs for a specific
> > range of inputs for the float values, it selects records which are outide
> > the inputs ranges
> >
> > For example, if the query is run for float values between 1 and 10 then
> > values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> > ?
> >
> > We tried using the float conversion part of the where clause error, but
> > it
> > gives a data type conversion error . Is there any other method to order
> > the
> > varchar values logically besides converting into float.
> >
> > Need forum members help on this
> >
> > Soura.
> >
> >
> >
>
>|||We have solved this issue by using money instead of float
"nate.vu@.gmail.com" wrote:
> Could you perhaps post the query so that we know what steps you are
> taking to accomplish your goal?
>|||Hi
You may want to use decimal or numeric instead of money.
John
"SouRa" wrote:
> We have solved this issue by using money instead of float
> "nate.vu@.gmail.com" wrote:
> > Could you perhaps post the query so that we know what steps you are
> > taking to accomplish your goal?
> >
> >sql sql

Conversion of Varchar into float

We convert a varchar column into float so that the data is ordered logically
like (1,2,10,11) instead of (1,10,11,2).
We have noticed one peculiar issue. When this query runs for a specific
range of inputs for the float values, it selects records which are outide
the inputs ranges
For example, if the query is run for float values between 1 and 10 then
values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
We tried using the float conversion part of the where clause error, but it
gives a data type conversion error . Is there any other method to order the
varchar values logically besides converting into float.
Need forum members help on this
Soura.Could you perhaps post the query so that we know what steps you are
taking to accomplish your goal?|||Hi
Check out http://www.sommarskog.se/arrays-in-sql.html to convert it into an
orderable format, you would then need to reconstitute the string.
John
"SouRa" wrote:

> We convert a varchar column into float so that the data is ordered logical
ly
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
?
> We tried using the float conversion part of the where clause error, but
it
> gives a data type conversion error . Is there any other method to order th
e
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||Do your numbers have decimals? If not, then you should be using int and not
float.
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> We convert a varchar column into float so that the data is ordered
> logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> ?
> We tried using the float conversion part of the where clause error, but
> it
> gives a data type conversion error . Is there any other method to order
> the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||We do have decmials .I forgot to mention about this in my original post
"Michael D'Angelo" wrote:

> Do your numbers have decimals? If not, then you should be using int and n
ot
> float.
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
>
>|||We have solved this issue by using money instead of float
"nate.vu@.gmail.com" wrote:

> Could you perhaps post the query so that we know what steps you are
> taking to accomplish your goal?
>|||Hi
You may want to use decimal or numeric instead of money.
John
"SouRa" wrote:
[vbcol=seagreen]
> We have solved this issue by using money instead of float
> "nate.vu@.gmail.com" wrote:
>

Conversion of int data type error?!

Hi,

I keep getting the error:

System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '@.qty' to data type int.

When I initiate the insert and update.

I tried adding a: Convert.ToInt32(TextBox1.Text), but it didn't work..

Could someone help?

My code:

private bool ExecuteUpdate(int quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

SqlCommand command = new SqlCommand();
command.Connection = con;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");
Label labname = (Label)FormView1.FindControl("Label3");
Label labid = (Label)FormView1.FindControl("Label13");

command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - '@.qty' WHERE productID=@.productID";
command.Parameters.Add("@.qty", TextBox1.Text);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();

con.Close();
return true;
}

private bool ExecuteInsert(String quantity)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True";

con.Open();

command.CommandText = "INSERT INTO Transactions (Usersname,Itemid,itemname,Date,Qty) VALUES (@.User,@.productID,@.Itemsname,@.date,@.qty)";
command.Parameters.Add("@.User", System.Web.HttpContext.Current.User.Identity.Name);
command.Parameters.Add("@.Itemsname", labname.Text);
command.Parameters.Add("@.productID", labid.Text);
command.Parameters.Add("@.qty", Convert.ToInt32(TextBox1.Text));
command.Parameters.Add("@.date", DateTime.Now.ToString());
command.ExecuteNonQuery();

con.Close();
return true;
}

protected void Button2_Click(object sender, EventArgs e)
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteUpdate(Int32.Parse(TextBox1.Text) );
}

protected void Button2_Command(object sender, CommandEventArgs e)
{
if (e.CommandName == "Update")
{
TextBox TextBox1 = FormView1.FindControl("TextBox1") as TextBox;
ExecuteInsert(TextBox1.Text);
}
}

Thanks so much if someone can!

Jon

Hi,

I think the problem lies in your Command Text. Try this:

command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable - " + @.qty +" WHERE productID=@.productID";

Hope this helps.

|||

Hi,

I tried it but it gave me a different error message saying qty doesnt exist.

But actually the update seems to work - I think the problem lies with the insert commands..

Thanks,

Jon

|||

In your original post, you need to remove the single quotes around @.qty.

|||

sswanner1:
In your original post, you need to remove the single quotes around @.qty.

Hi,

I put them in when I got a syntax error 'near WHERE'..

If I take them away the error comes back..

|||

command.Parameters.Add("@.qty", TextBox1.Text); //need to convert into integer like Convert.ToInt32(TextBox1.Text)

TextBox.Text is string rather than integer. You need to valify and convert it to integer.

|||

Hi,

You mean just change the update parameter to the same as the insert parameter (@.qty)?

If so, I have done that but it still gives the same error...

Thanks,

Jon

|||

int qty = 0;
TextBox TextBox1 = (TextBox)FormView1.FindControl("TextBox1");

if(TextBox1 != null)

{

qty = int.parse(TextBox1.Text);

}

catch{}

command.CommandText = "UPDATE Items SET Quantityavailable = Quantityavailable -@.qty WHERE productID=@.productID";
command.Parameters.Add("@.qty",qty);
command.Parameters.Add("@.productID", labid.Text);
command.ExecuteNonQuery();

Following my code, and do it for both methods. And, you need to do same for @.productId.|||

Hi,

che3358:
if(TextBox1 != null)
{
qty = int.parse(TextBox1.Text);
}
catch{}

Gives me a squiggly before catch{}, saying 'try' is expected? then when I type try it gives more syntax errors?

Thanks,

Jon

|||

You will have to convert TextBox1.text to int . Try using ,

int qty = int.parse(TextBox1.text);

command.Parameters.Add("@.qty",SqlDbType.Int);

command..Parameters["@.qty"].Value = qty ;

|||

My fault. It should be

if(TextBox1 != null) {
try
{
qty = int.parse(TextBox1.Text);
}
catch{}
}

|||

Hi again,

Now it gives the error:

CS0117: 'int' does not contain a definition for 'parse'

Line 40: qty = int.parse(TextBox1.Text);

??

Thanks again!

Jon

|||

int.Parse. Sorry.

|||

Hi,

New error:

CS0103: The name 'int32' does not exist in the current context

Line 40: qty = int32.parse(TextBox1.Text);

Cheers

Jon

|||

it needs to be exactly as :

qty = int.Parse(TextBox1.Text);

Tim

conversion from nText to varchar

Hi All,

Is it possible to convert a nText column in the source to varchar in the destination. I tried using a DataConversion block but there is no option for Ntext, I think am misising somehting here. Can someone guide me here?

thanks in advance,

Hi..

Use 'derived column'..

It is impossible to change nText column to varchar directly, but to change nText to text and text to varchar column is possible.

If [AAA] column is input, use this expression.

(DT_STR,4000,1252)((DT_TEXT,1252)AAA)

HTH.

ADConsulting / SQLeader.com / Daeseong Han

|||

I believe ntext is considered as unicode string. Please try that out.

Thanks,

S Suresh

|||

It works correctly, if input column type is unicode string type.

http://www.sqlleader.com/pds/board/ss2005ssis/editor/aaaa.jpg

Conversion from float to varchar

--SCRIPT :
CREATE TABLE [t1] (
[id] [float] NULL ,
[charid] [varchar] (10)
)
GO
INSERT INTO [t1] VALUES(1.0 , null )
INSERT INTO [t1] VALUES(3.1099999999999999 , null )
INSERT INTO [t1] VALUES(2.1000000000000001 , null )
What is required that copying data from column [id] to column [charid] with
all trailing decimal values.
-KhurramCREATE TABLE [#t1] (
[id] [float] NULL ,
[charid] [varchar] (10)
)
GO
INSERT INTO [#t1] VALUES(1.0 , null )
INSERT INTO [#t1] VALUES(3.1099999999999999 , null )
INSERT INTO [#t1] VALUES(2.1000000000000001 , null )
UPDATE #t1
SET charid =FLOOR(id)
Select * from #t1
HTH, Jens Suessmeyer.|||Look up the STR function in Books Online.
http://msdn.microsoft.com/library/d.../>
us_412q.asp
Plus some reading on data modeling might prove to be of great help.
MLsql sql

Thursday, March 22, 2012

Conversion failed when converting the varchar value

I keep receiving this error:

Conversion failed when converting the varchar value 'INSERT INTO temp_tableZ (Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, time_member_key, Revenue, Fiscal_Year ) select Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, ' to data type int

When running the code below. I believe the problem has something to do with the @.time_member_key in the dynamically created SELECT statement. However, I don't understand the problem or how to fix it.

Can anyone provide some advice?

thank you,

Erik

droptable temp_tableZ
go

createtable temp_tableZ(
Customer_Category_Key INTNULL,
Item_Category_Key INTNULL,
Sales_Rep_Key INTNULL,
Revenue moneyNULL,
time_member_key INTNULL,
Fiscal_Year INTNULL)
go

dropprocedure temp_testA
go

createprocedure temp_testA as
BEGIN
declare @.time_member_key asint
declare @.calendar_date_dt asdatetime
declare @.FY0YTD asMONEY
declare @.sql_string asnvarchar(1024)
set @.calendar_date_dt ='10/1/2005'
while @.calendar_date_dt <'9/30/2006'
BEGIN

select @.time_member_key = time_member_key
from dim_time
where @.calendar_date_dt = dim_time.calendar_date_dt

SET @.sql_string =
'INSERT INTO temp_tableZ'+' ('+
'Customer_Category_Key, '+ 'Item_Category_Key, '+ 'Sales_Rep_Key, '+ 'time_member_key, '+
'Revenue, '+ 'Fiscal_Year '+ ') '+

'select Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, '+ @.time_member_key +', sum(Revenue), 2006'+char(13)+
'from Fact_Sales t1, dim_time t2 '+char(13)+
'where t1.time_member_key = t2.time_member_key'+char(13)+
'and t1.time_member_key < '+ @.time_member_key +char(13)+
'and t2.Fiscal_Year = 2006'+char(13)+
'group by Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, '+ @.time_member_key +char(13)+
'go '

EXECsp_executesql @.sql_string

set @.calendar_date_dt = @.calendar_date_dt + 1

END

execute temp_testA

You need to convert @.time_member_key to an NVARCHAR(x), see below.

Read this document on 'Data Type Precedence' to find out why:

http://msdn2.microsoft.com/en-us/library/ms190309.aspx

Chris

SET @.sql_string =

'INSERT INTO temp_tableZ' +' (' +

'Customer_Category_Key, ' + 'Item_Category_Key, ' + 'Sales_Rep_Key, ' + 'time_member_key, ' +

'Revenue, ' + 'Fiscal_Year ' + ') ' +

'select Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, ' + CAST(@.time_member_key AS NVARCHAR(20)) + ', sum(Revenue), 2006' + char(13) +

'from Fact_Sales t1, dim_time t2 ' + char(13) +

'where t1.time_member_key = t2.time_member_key' + char(13) +

'and t1.time_member_key < ' + CAST(@.time_member_key AS NVARCHAR(20)) + char(13) +

'and t2.Fiscal_Year = 2006' + char(13) +

'group by Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, ' + CAST(@.time_member_key AS NVARCHAR(20)) + char(13) +

'go '

Conversion error

I am getting the error message Error converting data type varchar to float when running the following query:

Code Snippet

select top 175816
AV.intItemID,
AV.intAttrID,
-- AV.vchValue,
CAST(AV.vchValue AS float) AS Test,
0
from tblAttrVals AV
join tblAttributes AA
on AA.intAttributeID = AV.intAttrID
and AA.intDataTypeID in (2, 3)
and (1 = isnumeric (AV.vchValue))
order by AV.intItemID, AV.intAttrID

Here is what is strange. If I bump the top count down by one it succeeds. And even stranger, if I leave the top count the same and uncomment out the line in the select statement that shows the value being converted it succeeds.

Any ideas? This seems like a bug.

Chris:

Can you show us the specific data that is giving you trouble?

|||

I found the issue. It actually had nothing to do with the data that is being returned. It had to do with the data not being returned.

Here is the info from a post that helped me:

The problem is that SQL Server 2005 is more aggressive in terms of evaluating expressions in your query and moving them to different stages of the query plan. This might result in conversion error like in your case if the CAST gets computed before the WHERE clause checks. So there is no guarantee that the expressions in the WHERE clause will be computed first. This was true even in SQL Server 2000 except that you probably never hit it for your schema/data set. You can get the same error there also if the query plan changes.

To resolve the problem, you need to either correct your data model to represent the values correctly. Use float if your data is float - don't mix values from different domains. Or you will have to use CASE in the SELECT list to avoid the conversion problem. Note that using CASE expression is the only way to control order of execution of various expressions. See link below for more details (search for unsafe expressions):

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

To summarize you have two solutions:

1. Fix your data model / schema so you represent the values in their proper domain (not float values in varchar and mixing various values in string)
2. Or modify your SELECT in the 2nd view to:

SELECT cast(CASE WHEN dwpId LIKE '[0-9]%' THEN dwpId END as int) as dwpId, startDate, endDate
FROM View1

Note that even above check is not entirely correct because not all values that have just numeric digits can be successfully converted to int. You might get overflow errors for example. You could use ISNUMERIC but that checks for integer, numeric, and money conversions so it will let more data through. So it is best you correct your schema to avoid all these issues.

sql sql

conversion char/nchar

Hi,
We are using sql server 2000..We intend to change the
char/varchar columns to nchar/nvarchat.Our application
contains lot's of dynamic tables.Hence there are no
standard no of tables and indexes in all sites..Can any
one help me out in writing scripts which queries the
dictionary objects and gives scripts which would work fine
in all sites...?
Thanks in advance
SridharThis script should get you started:
use tempdb
GO
CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar(10), thircol =char(10))
go
SELECT 'ALTER TABLE ' + table_name +
' ALTER COLUMN ' + COLUMN_NAME +
CASE WHEN DATA_TYPE =3D 'char' THEN ' nchar ' ELSE ' nvarchar ' =END + ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
char(13) + char(10) + 'GO'
FROM information_schema.columns WHERE DATA_TYPE IN ('varchar', 'char')
--now execute the statements that are returned from the select statement
go
DROP TABLE foo
-- Keith
<anonymous@.discussions.microsoft.com> wrote in message =news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> Hi,
> > We are using sql server 2000..We intend to change the > char/varchar columns to nchar/nvarchat.Our application > contains lot's of dynamic tables.Hence there are no > standard no of tables and indexes in all sites..Can any > one help me out in writing scripts which queries the > dictionary objects and gives scripts which would work fine > in all sites...?
> > > Thanks in advance
> > Sridhar
>|||Hi,
Thanks..do some where sql server stores the index and
constraints structure some where in dictionary ...other
wise how do i recreate the indexes and constraints after
converting to nchar
Sridhar
>--Original Message--
>This script should get you started:
>use tempdb
>GO
>CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
(10), thircol char(10))
>go
>
>SELECT 'ALTER TABLE ' + table_name +
> ' ALTER COLUMN ' + COLUMN_NAME +
> CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
nvarchar ' END +
> ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> char(13) + char(10) + 'GO'
>FROM information_schema.columns WHERE DATA_TYPE IN
('varchar', 'char')
>--now execute the statements that are returned from the
select statement
>go
>DROP TABLE foo
>
>
>--
>Keith
>
><anonymous@.discussions.microsoft.com> wrote in message
news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
>> Hi,
>> We are using sql server 2000..We intend to change the
>> char/varchar columns to nchar/nvarchat.Our application
>> contains lot's of dynamic tables.Hence there are no
>> standard no of tables and indexes in all sites..Can any
>> one help me out in writing scripts which queries the
>> dictionary objects and gives scripts which would work
fine
>> in all sites...?
>>
>> Thanks in advance
>> Sridhar
>.
>|||SQL Server stores this information in system tables, like sysindexes, syscomments etc. You can read off of
these and use that information to re-generate the statements needed to re-create your stuff. Or script the
stuff: http://www.karaszi.com/sqlserver/info_generate_script.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
<anonymous@.discussions.microsoft.com> wrote in message news:898401c432a5$31453bb0$a601280a@.phx.gbl...
> Hi,
> Thanks..do some where sql server stores the index and
> constraints structure some where in dictionary ...other
> wise how do i recreate the indexes and constraints after
> converting to nchar
> Sridhar
> >--Original Message--
> >This script should get you started:
> >
> >use tempdb
> >GO
> >CREATE TABLE foo (firstcol varchar(10), secondcol nvarchar
> (10), thircol char(10))
> >go
> >
> >
> >SELECT 'ALTER TABLE ' + table_name +
> > ' ALTER COLUMN ' + COLUMN_NAME +
> > CASE WHEN DATA_TYPE = 'char' THEN ' nchar ' ELSE '
> nvarchar ' END +
> > ' (' + LTRIM(STR(CHARACTER_MAXIMUM_LENGTH)) + ')' +
> > char(13) + char(10) + 'GO'
> >FROM information_schema.columns WHERE DATA_TYPE IN
> ('varchar', 'char')
> >
> >--now execute the statements that are returned from the
> select statement
> >
> >go
> >DROP TABLE foo
> >
> >
> >
> >
> >--
> >Keith
> >
> >
> ><anonymous@.discussions.microsoft.com> wrote in message
> news:869f01c4328f$e1ddd420$a401280a@.phx.gbl...
> >> Hi,
> >>
> >> We are using sql server 2000..We intend to change the
> >> char/varchar columns to nchar/nvarchat.Our application
> >> contains lot's of dynamic tables.Hence there are no
> >> standard no of tables and indexes in all sites..Can any
> >> one help me out in writing scripts which queries the
> >> dictionary objects and gives scripts which would work
> fine
> >> in all sites...?
> >>
> >>
> >> Thanks in advance
> >>
> >> Sridhar
> >>
> >.
> >

conversion char/nchar

Conversion between data types

Lets say I execute: SELECT hashbytes('MD5','IMTIAZ')

Which returns: 0x60D164C6B64EE81C7E7395C01D838FEE

How do I get a varchar: 60D164C6B64EE81C7E7395C01D838FEE

Not converted.

How do I get the 0x removed from the string ?

Regards

Imtiaz

I have been googling to find a solution to this question.....

I have acome across a few places to use the xp_varbintohexstr undocumented procedure. But in SQL 2005 what is the equivalent and any pointers in this direction will be of great help.

Regards

Imtiaz

|||

Ok...here's the answer...

SELECT substring(upper(master.dbo.fn_varbintohexstr(hashbytes('MD5','SHELLEY'))),3,len(master.dbo.fn_varbintohexstr(hashbytes('MD5','IMTIAZ'))))

|||You have to write your own TSQL/SQLCLR scalar UDF to do the conversion from varbinary to hexadecimal string. Please do not use undocumented stored procedures like xp_varbintohexstr or fn_varbintohexstr. Undocumented objects can be dropped or modified in any release or even service pack of SQL Server. So you should not rely on such interfaces. It is easier to write your own code for these type of problems.

Tuesday, March 20, 2012

Conversion

Hi,
I am in the process writing scripts for CHAR/VARCHAR to
NCHAR/NVARCHAR conversion..Infact i have completed
it..When I run these scripts against DB is verly
slow..This is happening when i run convert scripts
eaxctly..Even select name from sysobjects doesn't return
values..There are heavy IO is going on the DB..what are
the areas i should concentrate the to improve performance..
Basically I have written cursor whic executes one by one
the alter scripts..
Sridhar...Changing a CHAR to VARCHAR will most likely involve each row being affected
and hence all the I/O. If you are altering more than one column per table
you might get better performance by creating a second table with the changed
columns and Inserting all the rows into it. Drop the original, rename the
new to the old and add the appropriate indexes, RI etc.. Just make sure you
have good backups first.
Andrew J. Kelly SQL MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:297601c47e07$62bd4ae0$a501280a@.phx.gbl...
> Hi,
> I am in the process writing scripts for CHAR/VARCHAR to
> NCHAR/NVARCHAR conversion..Infact i have completed
> it..When I run these scripts against DB is verly
> slow..This is happening when i run convert scripts
> eaxctly..Even select name from sysobjects doesn't return
> values..There are heavy IO is going on the DB..what are
> the areas i should concentrate the to improve performance..
>
> Basically I have written cursor whic executes one by one
> the alter scripts..
>
> Sridhar...
>

Conversion

How do you convert a varchar into numeric?Look up CAST and CONVERT functions in Books Online.

blindman|||which books online. Cant somebody just give me an example|||convert(varchar_column as numeric)

where
isnumeric(varchar_column) = 1|||try this

declare @.test varchar(2)
set @.test = '02'
select cast(@.test as numeric)|||Originally posted by rali08
which books online. Cant somebody just give me an example

Figuring out what's meant by BOL (books online) would server you better than a simple example...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/getstart_4fht.asp|||Give a man a fish and you feed him for a day.
Teach a man to fish (for information in books online) and you feed him
for the rest of his life (or career).

blindman|||Stuff yourself:

all you can eat fish (http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp)|||Doesn't highlighting + Shift-F1 in QA come natural? It has been working since 6.5!!|||Can't somebody press Shift-F1 for me?|||Originally posted by rdjabarov
Doesn't highlighting + Shift-F1 in QA come natural? It has been working since 6.5!!

No need to...never close BOL...never turn off the computer...ok once a month...

Does anyone still use CTRL-E?

I don't think it's documented..if they make me do F5 only Yukon...8-(

How about this...

..code [ENTER]
[SHIFT] Up Arrow
[CTRL]-E|||Holy moly! That shift + f1 thing even works in the results pane. Now all I have to do is teach some of these college-hires how to pick what to highlight :-P|||I USE CTRL+E ALL THE TIME!!! And Ctrl+Break, and Ctrl+S, and Alt+F4, and Shift+Tab, and...whatever, it's all handy.|||Ok - Here you go - "My fellow nerds and I will retire to the nerdery..."

SQL Query Analyzer Keyboard Shortcuts
This table displays the keyboard shortcuts available in SQL Query Analyzer.

Activity Shortcut
Bookmarks: Clear all bookmarks. CTRL-SHIFT-F2
Bookmarks: Insert or remove a bookmark (toggle). CTRL+F2
Bookmarks: Move to next bookmark. F2
Bookmarks: Move to previous bookmark. SHIFT+F2
Cancel a query. ALT+BREAK
Connections: Connect. CTRL+O
Connections: Disconnect. CTRL+F4
Connections: Disconnect and close child window. CTRL+F4
Database object information. ALT+F1
Editing: Clear the active Editor pane. CTRL+SHIFT+DEL
Editing: Comment out code. CTRL+SHIFT+C
Editing: Copy. You can also use CTRL+INSERT. CTRL+C
Editing: Cut. You can also use SHIFT+DEL. CTRL+X
Editing: Decrease indent. SHIFT+TAB
Editing: Delete through the end of a line in the Editor pane. CTRL+DEL
Editing: Find. CTRL+F
Editing: Go to a line number. CTRL+G
Editing: Increase indent. TAB
Editing: Make selection lowercase. CTRL+SHIFT+L
Editing: Make selection uppercase. CTRL+SHIFT+U
Editing: Paste. You can also use SHIFT+INSERT. CTRL+V
Editing: Remove comments. CTRL+SHIFT+R
Editing: Repeat last search or find next. F3
Editing: Replace. CTRL+H
Editing: Select all. CTRL+A
Editing: Undo. CTRL+Z
Execute a query. You can also use CTRL+E (for backward compatibility). F5
Help for SQL Query Analyzer. F1
Help for the selected Transact-SQL statement. SHIFT+F1
Navigation: Switch between query and result panes. F6
Navigation: Switch panes. Shift+F6
Navigation: Window Selector. CTRL+W
New Query window. CTRL+N
Object Browser (show/hide). F8
Object Search. F4
Parse the query and check syntax. CTRL+F5
Print. CTRL+P
Results: Display results in grid format. CTRL+D
Results: Display results in text format. CTRL+T
Results: Move the splitter. CTRL+B
Results: Save results to file. CTRL+SHIFT+F
Results: Show Results pane (toggle). CTRL+R
Save. CTRL+S
Templates: Insert a template. CTRL+SHIFT+INSERT
Templates: Replace template parameters. CTRL+SHIFT+M
Tuning: Display estimated execution plan. CTRL+L
Tuning: Display execution plan (toggle ON/OFF). CTRL+K
Tuning: Index Tuning Wizard. CTRL+I
Tuning: Show client statistics CTRL+SHIFT+S
Tuning: Show server trace. CTRL+SHIFT+T
Use database. CTRL+U|||Why is Alt + X (Execute Query) not listed over here in the shortcuts ??|||Originally posted by Enigma
Why is Alt + X (Execute Query) not listed over here in the shortcuts ??

I did not know that...at first I thought you meant CTRL-X...|||Only thing I can add to that list is ctrl-tab switches between existing connections.

And Brett. I live by alt-X, so if microsoft takes it away, I will have to be retrained, depending on what alt-x does in the new version.|||I second that !!!!
And Brett. I live by alt-X, so if microsoft takes it away, I will have to be retrained, depending on what alt-x does in the new version.

Thursday, March 29, 2012

Convert AlphaNumeric to Numeric

Tuesday, March 27, 2012

convert a datatype

Convert $ to varchar decimal problem

convert

Convert

convert

conversion to date

Sunday, March 25, 2012

Conversion of Varchar into float

Conversion of Varchar into float

Conversion of int data type error?!

conversion from nText to varchar

Conversion from float to varchar

Thursday, March 22, 2012

Conversion failed when converting the varchar value

Conversion error

conversion char/nchar

conversion char/nchar

conversion char/nchar

Conversion between data types

Tuesday, March 20, 2012

Conversion

Conversion

Consolidation Query

Blog Archive

About Me