Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks
Hello all,Currently i am having problem with the conversion from object to string. I am using SqlCommand to draw data from DB, and the statement i used is:String ires = myCommand.ExecuteScalar().ToString();and then convert ires to int by int.Parse(ires) I have got no problem during the compilation, but it gives me the exception during the runtime...any one got any ideas? Thanks
I'm working with an SqlDataSource, and I'm just wondering how to get the data to a String if I'm sure the select statement will only return 1 piece of data. Any ideas?
hi , i have a problem i have an textarea that i want to convert to DateTime format (dd/MM/yyyy) . the data in the textarea is (dd/MM/yyyy for example 21/12/2005). i need it to add this data in sql server , in smalldatetime formation colum . plz help.
Hi folks, I'm trying to import data from a text file (UnicodeData.txt) into an SQL table. Some of the fields are unicode values (16 bits) expressed as four hexidecimal digits. I've succeeded in importing the data as character strings, but I have not found a way to convert them into numbers. (They could be stored as int or nchar.) I've tried convert(binary/int/whatever, string); E.g. select convert(int, '0x1111') from import_unicode gives the error Syntax error converting the varchar value '0x1111' to a column of data type int.
I could write code to strip off one character at a time, convert the hex digit to a decimal value, shift left, etc., but I find it hard to believe that's the best way.
Any help is appreciated. Please email answer to lars_huttar@sil.org as I don't read this board.
I am using DTS to read in a date with the format of YYYYMMDD. I am trying to convert this date to MM/DD/YYYY and then use the CDate function with the following code to load it into a Datetime column in SQL Server:
DTSDestination("DateName") = strHoldDate Main = DTSTransformStat_OK End Function
My DTS package will execute without errors, but it does not add the row. I have been successful using CDate when the source date is in the format MM/DD/YYYY.
Also, do you have any tips on how to debug DTS? How to see what's in a variable, etc.?
To start, I am NOT a SQL programmer. I have to do some minimal SQL administration (DB Creation, Backups, Security) on spatial databases that are for the most part managed by a 3rd party program. My experience with T-SQL is mostly simple tasks (i.e. Select and Update statements)..However I have been requested to calculate an ID Field using the values of two other fields. Per the request, I need to convert one field to Hex and concatenate with the second field.
ex. Field 1 + Field 2(hex string) = Field 3 Field 1 = 'FF02324323' Field 2 = 'Smith Creek' Field 3 = 'FF02324323536D69746820437265656B'
Field 1 VarChar(10) (Code) Field 2 VarChar(65) (Common Name) Field 3 VarChar(max) (ResourceID)
Spent half the day searching and have tried various forms of CAST, CONVERT, fn_varbintohexstr and others but have unable to come up with the correct combination to get what I need.
In our ERP system we have a field which is a date-picker in the user front end, but the value is stored in an NVARCHAR field and not always consistently. How can I convert this to a date (preferably in the format YYYY-MM-DD HH:MM) that I could use in a calculation?
select code, spec_value from spec_checklist_remind where spec_checklist_id = 17
Hi all, I defined an user string type varible in the package as AccountLen. I am trying to use this varible in the Expression of Derived Column transformation. I want to retrieve a part of column, i.e: Right(Column1, @AccountLen), this is always wrong because the AccountLen is string type. How I can convert it to the numeric so that can be used in the RIGHT function? Thanks
Hi Everyone - iam facing a small problem i want to convert an output from the context_info() which is binary to string (or int)
example:
SET CONTEXT_INFO 3
select CONTEXT_INFO() --- it will return 0x0000000300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
I am getting an issue when i try to run a package which was validated against an oracle database on windows with UTF8. When I try to run the same package(no changes, just changing the conenction parameters to point to an oracle database on UNIX with UTF16), the package fails with error saying cannot convert unicode to string for string columns. The same run through fine if I refresh the metadata against the UNIX database, but then it doesnt run against the Windows database.
Can anyone please suggest how to overcome this scenario. My issue is, I need to run my package against various database(by just pointing the database connection details) without changing anything in the package.
I'm trying to figure out how to convert an INT so that I can add it to another string in a statement likeSELECT '<a href="http://mydomain.com/' + [IntValue] + '">' + [LinkName]Where [IntValue] is an INT field containing a folder number. I tried: TO_CHAR([IntValue, '999')but it didn't work. And I tried to find a TO_STRING function, but had no luck.Any advice to someone who is savy, but new to SQL Queries?
Hi, I want to convert literal string to unicode before insert into the database. and after insertion i want to retrive this value from data base and convert back to literal string. pls tell me how to incode and decode literal string to unicode and from unicode to literal string.
Hi Everyone,My SQL Query is :"select Field1 from table1"The Field1 in Database is of Type "nvarchar" . I need to convert this Field into "integer" in the Query itself.Please Help!!I have already tried "Select convert(int, Field1 ) from table1"The field gives zero output ThanksRegardsNavdeep
i have a sql statement that i created in code and it is sending a query to the database when i dim the variable a datetime variable it says that it cant convert it if i make the variable a varchar it works but it only returns one result when it should be returning about 10
here is the code
Public Function dbDGQSSearch(ByVal BatchID As String, ByVal CreatedBy As Integer, ByVal CreatedFor As Integer, ByVal DateCreatedMod As Integer, ByVal DateCreated As String, ByVal DateCompletedMod As Integer, ByVal DateCompleted As String, ByVal DateStartedMod As Integer, ByVal DateStarted As String, ByVal SearchType As Integer, ByVal Completed As Integer, ByVal PriorityMod As Integer, ByVal Priority As Integer, ByVal RemainingCallsMod As Integer, ByVal RemainingCalls As Integer, ByVal TotalCallsMod As Integer, ByVal TotalCalls As Integer, ByVal Bonus As Integer, ByVal Keyword1 As String, ByVal Keyword2 As String, ByVal Keyword3 As String, ByVal Keyword4 As String, ByVal Keyword5 As String)
Dim strQueSearch As String strQueSearch = "SELECT tlkup_Rep.RepID, tlkup_Rep.PositionID, tlkup_Rep.RepFName, tlkup_Rep.RepLName, tlkup_Rep.RepPassword, tlkup_Rep.RepUserName, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName, t_Que.QueID, t_Que.BatchID, t_Que.AdminID, t_Que.Manager, t_Que.BonusID, t_Que.QueCompleted, t_Que.QueDate, t_Que.QueNotes, t_Que.QuePriority, t_Que.QueQuantity, t_Que.QueStartDate, t_Que.Mail, t_Que.QueDateComplete, t_Que.QueTotal FROM t_Que INNER JOIN tlkup_Rep ON t_Que.Manager = tlkup_Rep.RepID AND t_Que.Manager = tlkup_Rep.RepID WHERE BatchID<>'' and BatchID<>'2' and BatchID<>'3' and BatchID<>'4' "
'Creates statement for selecting the add to batch data where the criteria appear If BatchID <> "" Then
strQueSearch = strQueSearch + " and t_Que.BatchID= @BatchID " End If If CreatedBy > 1 Then strQueSearch = strQueSearch + " and t_Que.RepID =@RepID " End If If CreatedFor > 1 Then strQueSearch = strQueSearch + " and t_Que.Manager = @Manager " End If
If DateCreated <> "" Then If DateCreatedMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QueDate >@QueDate " ElseIf DateCreatedMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QueDate <@QueDate " ElseIf DateCreatedMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QueDate =@QueDate " End If End If
If DateCompleted <> "" Then If DateCompletedMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QueDateComplete >@QueDateComplete " ElseIf DateCompletedMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QueDateComplete <@QueDateComplete and t_Que.QueDateComplete >'1/1/1900' " ElseIf DateCompletedMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QueDateComplete =@QueDateComplete " End If End If
If DateStarted <> "" Then If DateStartedMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QueStartDate >@QueStartDate " ElseIf DateStartedMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QueStartDate <@QueStartDate " ElseIf DateStartedMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QueStartDate =@QueStartDate " End If End If
If SearchType = 0 Then 'Both 'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 " ElseIf SearchType = 1 Then 'Mail strQueSearch = strQueSearch + " and t_Que.Mail=1 " ElseIf SearchType = 2 Then 'Phone strQueSearch = strQueSearch + " and t_Que.Mail=0 " End If
If Completed = 0 Then 'Both 'strQueSearch = strQueSearch + " and t_Que.Mail=0 and t_Que.Mail=1 " ElseIf Completed = 1 Then 'Yes strQueSearch = strQueSearch + " and t_Que.QueCompleted=1 " ElseIf Completed = 2 Then 'No strQueSearch = strQueSearch + " and t_Que.QueCompleted=0 " End If
If Priority > 0 Then If PriorityMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QuePriority >@QuePriority " ElseIf PriorityMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QuePriority <@QuePriority " ElseIf PriorityMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QuePriority =@QuePriority " End If End If If RemainingCalls > 0 Then If RemainingCallsMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QueQuantity >@QueQuantity " ElseIf RemainingCallsMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QueQuantity <@QueQuantity " ElseIf RemainingCallsMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QueQuantity =@QueQuantity " End If End If
If TotalCalls > 0 Then If TotalCallsMod = 0 Then '> strQueSearch = strQueSearch + " and t_Que.QueTotal >@QueTotal " ElseIf TotalCallsMod = 1 Then '< strQueSearch = strQueSearch + " and t_Que.QueTotal <@QueTotal " ElseIf TotalCallsMod = 2 Then '= strQueSearch = strQueSearch + " and t_Que.QueTotal =@QueTotal " End If End If
If Bonus > 1 Then strQueSearch = strQueSearch + " and t_Que.BonusID =@BonusID " End If
If Keyword1 <> "" Then strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword1+'%' " End If If Keyword2 <> "" Then strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword2+'%' " End If If Keyword3 <> "" Then strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword3+'%' " End If If Keyword4 <> "" Then strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword4+'%' " End If If Keyword5 <> "" Then strQueSearch = strQueSearch + " and t_Que.QueNotes like '%'+@Keyword5+'%' " End If
'makes statement into sqlcommand C.daQueSearch.SelectCommand.CommandText = strQueSearch
Hello I'm trying to write a SQL Statement along the lines of....
SELECT stringField + ' : ' + STR(decimalField) AS myField FROM tablename WHERE myCondition = myValue
Where stringField is a String field and decimalField is a Decimal Field in my Table. In this statement it converts the decimal field to a string value so that it doesn't throw a conversion error but unfortunatly it seems to round up the value to an integer value and cuts off all my decimal places.
How do I convert for example, a value comming from C# app "TextboxGBDataDeleted" to int column GBdatadeleted? I have a RangeValidator on this textbox to accept only numbers. I don't want to write C# code. I woul prefer to do this is SQL. Thank you.
