hi could u help me in suggesting how to convert number given in Rs or in $ to words as normal we spell.. ther's is no limit it can be billions or millions.. thanks in advance..
hi , I want to create a function in sql server 2000 which can display the given input currency into words but in indian format eg , '12345678.50' should display as One crore twenty lacs fourty five thousand six hundred and seventy eight rs fifty paise only . Can anybody help pls
I design a report have financial fields for the employees salary. now i need to convert the salary amount from numbers to words in English and in Arabic.
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50) set @id= '3,4,6,7' set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
I have a table with 2 columns and my source data looks like this..
PolicyNum  InsCode   Â
1ABC12Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1004Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum  InsCode1  InsCode2   Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â 1006Â Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â 1004Â Â Â Â Â Â Â 1ABC20Â Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005Â Â Â Â Â Â Â Â Â Â Â Â Â InsCode value 1002 should always mapped to 1006 Â Â Â Â Â Â Â Â Â Â Â InsCode value 1003 should always mapped to 1004Â
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null) Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')Â Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')Â Â Â Â Â Â Â Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')Â Â Â Â Â Â
I've got a view which returns Null values due to an left outer join. The field which sometimes returns Nulls is called ClientCount. I want to create a new field which displays the value of ClientCount if ClientCount is not null and 0 if it is null.
In MS Access I use the following: IIF([ClientCount] Is Null,0,[ClientCount]) This does not seem to work in MS SQL. Is there some equivalent function I could use?
Account Number Balance Percent 45678935 4596.00 0.3500 78965215 56.43 1.0000
This seems like it should be ridiculously easy, but I keep running into road blocks. Like I said, this is just a sample from my table. My real table has 90 columns in it and about half of them need to be changed to either a dollar representation or a percent.
I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00. example of data: 0001028.99 - needs to be shown as 1028.99 222.00 - needs to be shown as 222.00 0000190.89 - needs to be shown as 190.89 2708.99 - needs to be shown as 2708.99 00000-50.99 - needs to be shown as -50.99 -109.79 - needs to be shown as -109.70
as you can see some of the values have leading zeros and some don't. i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.
Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.
Right now I have a cursor that makes me want to puke. This is the last cursor in my current project and I want to replace it with a much faster set based operation.
Here is the problem. I have a table with say 1-3 million records. There are fields that get loaded in with date information. These fields are varchar because the date information could very well be mangled data that needs to be reviewed by a user. What I need is to go through these varchar fields and flag the values that cannot convert to smalldatetime.
I have another table that houses the primary key and the field of the record that cannot convert.
Essentially, I have a series of filters that run and flag using set based stored procedures. If there is a record that gets through that contains a value that cannot be converted, I have a cursor that steps through the data and attempts to convert the value. If it is able to be converted, then it continues on until it finds the value that is holding up the conversion.
I guess if I can run a query that will return all records that can convert for the field (or can't convert) I'd be all set. Any help here is appreciated.
I've recently discovered the MS CRM 3.0 stamps any datetime field in SQL as UTC (aka GMT). Even though the end user may select 12:00 noon on the CRM gui interface, the datetime is stamped in SQL as 16:00 (I'm in EST).
So, is there an easy way to read the 16:00 back to local EST, taking into account daylightsavings (DST)? Apparently in VB or C# this conversion is a trivial matter of using ToLocalTime(), but I don't believe SQL 2005 has such a convenient function.
I'm currently building a SQL view that I wish to use to support multiple reports, and it would be ideal for the view to translate the UTC datetime fields (even if via a user-defined function) to EST rather than coding every report or custom app to translate the UTC.
Any suggestions or links to code will be greatly appreciated!
I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto period6_subject_id.
Please see my query below:-
SELECT   Week_Day.Week_Day_name, Subject.Subject_Code,  Daily_Timetable.Period_No FROM     Week_Day LEFT JOIN            Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6  LEFT JOIN            Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by  Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
Using a reporting services model/report builder we have two related tables: - Fundings, (parent) - Draws, (child)
Report Builder reports that subtract "Total Fundings.Amount", (which is SUM(FundingAmount)) from "Total Draw Amount", (which is SUM(DrawAmount)) to get a balance work as expected except when there are no Draw rows, in which case a NULL is returned. Obviously we want to convert NULL values of "Total Draw Amount" to zero so that when subtracted from "Total Fundings.Amount" the correct value is displayed. I've searched for a function similar to COALESCE (Transact-SQL) in report builder but found nothing.
SELECT Â Â Â Â Â Â Â Â Â ID1, Â Â Â Â Â Â Â Â Â ID2, Â Â Â Â Â Â Â Â Â Â type, Â Â Â Â Â Â Â Â Â (case when type = '1' then sum(value) else '0' end) as Value1, Â Â Â Â Â Â Â Â Â (case when type = '3' then sum(value) else '0' end) as Value2, Â Â Â Â Â Â Â Â Â Â (case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3 FROM table1 WHEREÂ ID1 = 'x' and ID2= 'y'Â GROUP BY ID1, ID2, Type
Which returns: ID1    ID2       Type    Value1    Value2    Value3 005   11547   0         0.00        0.00       279.23 005   11547   1         15.23      0.00       0.00 005   11547   3         0.00        245.50    0.00
And I want to obtain this result: ID1Â Â Â Â ID2Â Â Â Â Â Â Â Value1 Â Â Â Value2 Â Â Â Value3 005Â Â Â 11547Â Â Â Â 15.23Â Â Â Â Â Â 245.50Â Â Â Â 279.23
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
Hello i need to know hoy to use the LIKE operator to find results that contains 2 or more words. ================TABLE EXAMPLE====================== I HAVE A TABLE CALLED ITEMS
ITEMNAME Good Bike Good Mountain Bike Klein Bike Mountain ===================================================
If i use SELECT ITEMNAME FROM ITEMS WHERE ITEMNAME LIKE '%Good Bike%' i only get: Good Bike
What to code i need to write if i want to get that results for QUERY: "Good Bike" returns Good Bike Good Mountain Bike
Hi I'm using the full-text indexing on a table and I'm trying to implement a search where users can search for words and use wildcards themselves. However I'm working on a method so that can enter a wildcard in the middle of a word to get records where they are unsure of the spelling etc. For instance, a search of 'Ste*en' should return results like 'Steven' and 'Stephen' etc. So if they are searching for word 'establishment' they can search for 'estab*ment' and it should return all the records using this query: SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*ment"') If I do a wildcard at the end e.g: SELECT * FROM myTable WHERE CONTAINS(myField,'"estab*"') I get the results I am looking for. But the middle wildcard does not seem to work as expected even though it is the syntax used on MSDN and other SQL info sites. Is there something I am not doing properly?
hi i am working on sql server200.I m using "LIKE" to search the records.There is freetexttable and containstable table also.just like to know the difference between them.Could anyone provide me a good link regarding this??Thanks
Hi There, I've created a couple of search pages which look at sql server. whenever words or values like "?@~:&£^" etc, or words like for, the and so forth, the page the nasrty error page: Execution of a full-text operation failed. A clause of the query contained only ignored words Exception Details: System.Data.SqlClient.SqlException: Execution of a full-text operation failed. A clause of the query contained only ignored words. In short: is there a way I can stop it doing this. It looks rather horrible. I've looked at form validation but cant find anything that seems to fit. I would imagine there is a simple solution, but I haven't been able to find it so far. Many thanks Stuart
How can you search for the occurance of a whole word in a string? but not return any results that have the word as a substring.
For instance, if I search for the term 'scene' in a column. Then it will only return rows that have the word 'scene' and not those with the word 'scenery'. I've tried the following sql, but it relies on having text either side of the word as well. If the word 'scene' is on the begining or end of the cell then it is not returned.
SELECT Name, Description FROM tblWine WHERE Name LIKE '%[^a-zA-Z]scene[^a-zA-Z]%' OR Description LIKE '%[^a-zA-Z]scene[^a-zA-Z]%'
Can I define field names with more words in Access and SQL Server likefield: "Bus station" instead "BusStation" or "Bus_Station"? I have hadproblems because of this in VB6. Can I have problems in VB 2005 or C# 2005and SQL Server?
Hi,I'm trying to read a varchar(50) field writed in Japanese using thissentence:is = rset.getBinaryStream(num);at that sentence the JDBC driver shows the following error:java.sql.SQLException: [Microsoft][SQLServer 2000 Driver forJDBC]Unsupported data conversion.Does anybody know why?Thank you,--__________________________________________Emilio PerezJoin Bytes!SINERGIA TECNOLÓGICAC/ Eusebio Sempere 1, Entreplanta A30003 AlicanteTel. 965 136 191www.sinergiatec.com__________________________________________La información incluida en el presente correo electrónico es CONFIDENCIAL,siendo para el uso exclusivo del destinatario arriba mencionado. Si ustedlee este mensaje y no es el destinatario señalado, el empleado o el agenteresponsable de entregar el mensaje al destinatario, o ha recibido estacomunicación por error, le informamos que está totalmente prohibidacualquier divulgación, distribución o reproducción de esta comunicación, yle rogamos que nos lo notifique, nos devuelva el mensaje original a ladirección arriba mencionada y borre el mensaje. Gracias.
Is it possible with SQL Server 2005 to include ignored words in a full-text search? For example, searching for "in force as of"? This gives the same results as searching for "force" only. I've tried to empty the ignored words list (noiseENG.txt), but this does not seem to have any effect.
Also we want to be able to search for strings such as "205/1305-2". Searching with punctuation characters in a query seems to be a problem.
What are the possibillities in SQL Server 2005 with regard to these problems?
Can someone point me in the right direction to write a select query to return the first 10 whole words from a table?
For example, table "testtable" contains a field named "description" with value "here is some test data in order to select the first full ten words from."
The SELECT statement would return the value "here is some test data in order to select the".
I need to build a search function for my site. So there is a single text box for the users to type in their search string. I have been asked that I need to break the user's search string into separate words. So if the user enters: "This is my search query", I need to break it into: "This" "is" "my" "search" "query" and then search for all these words.- Can I break a string into separet words using SQL?- How do I remove funny / dangerous characters from the search string?- If I have to break the search string using a programming language, I would have to run the search query for each word. If I run the search query for each word, How do I combine the search result for the user. For instance, if I search for "my" and find some result, then search for "search" and find some results, how do I display a SINGLE search result to the user.thanks