Function To Match Similar Words
Mar 30, 2007Is there a function to match simalar words
say for example 'tom' will match 'tomy'
Thanks
ADVERTISEMENT
Function Similar To ISNULL()
Oct 6, 2005I'm constructing a single string of several counts with concatenated labels using SQL and want to not show zeros (or their labels). Is there a function within an SQL statement that will let me do this? ISNULL() sort of does this, but I really need to test for zero instead of NULL to eliminate noise data from the string.
View 2 Replies View RelatedSimilar To COALESCE Function
Nov 4, 2004I have three fields in a table say [F1, F2 & F3]. I need to fetch anyone of these three fields which has the maximum value between them.
In Simple words i'm looking for some function which is similar to COALESCE function which returns the first NOT NULL value of the fields that were passed as arguments.
FYI I'm using SQL Server 7.0 which does not supports UDF's
Earlier response appreciated
Thanks and Regards
Chandru
Any Function In T-SQL Thats Similar To GREATEST() IN Orcl.
Dec 6, 2007
Is there any Function in T-SQL thats similar to GREATEST() IN Orcl?
Iam trying to find the greatest of two DATETIME datatype Fields in my 'select's.
If there is no in built function, Whats the best approach?
Conditonal SUM Function, Or Similar Conditional Aggregates
Jul 23, 2005Are there any conditional aggregate functions, such as SUM()?An example would probably be the best way to describe what I'mtrying to do...I have the following table, named Orders, with the following records:ItemNo qty_ord paid------ ----------- ------T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 199.00T101B 1 0.00T101B 1 0.00T101B 1 0.00T101B 1 0.00Z200L 1 50.00Z200L 2 100.00I want to produce the following result set:ItemNo qty_gross qty_net------ ---------- -------T101B 11 7Z200L 3 3The "qty_gross" column in the result set is the sum oftotal items ordered within the ItemNo grouping.Easy enough. However, I also want a column "qty_net" thatis the sum of qty_ord but ONLY IF the amount in the"paid" column is > 0.I tried using the HAVING clause, but that produces acatch 22 situation. If I say "HAVING paid > 0" thenthe qty_gross column is wrong because it leaves out rowsthat contain records with paid = 0 values. If I leaveout the HAVING clause, then the "qty_net" is wrong.Any ideas?select ItemNo, Sum(qty_ord) as qty_gross, Sum(qty_ord) as qty_netfrom Ordersgroup by qty_ord, paid, ItemNohaving paid > 0 ?????Thanks,Robbie
View 1 Replies View RelatedDoes SQL Server 2005 Has Similar Function Like Mysql_fetch_row ?
Mar 13, 2008I just began to use SQL Server 2005 as database programming and
found out that I have to translate mysql_fetch_row into SQL Server 2005
but I cannot find some related functions/api, and I was wondering
Does SQL Server 2005 has similar function like mysql_fetch_row ?
Or if not, any advice how I can program to acheive similar functions ?
thank you in advance.
Script / Function ... To Find Difference B/w 2 Similar Tables
Jul 23, 2005Hi,I m searching for some Script / Function ... to find difference in datab/w 2 similar tables (exactly same fields structure) in sql 2000.plz update me asap !thanks in advance !
View 2 Replies View RelatedHow To Basically Copy Tables With New Names Rather Than Create Similar Tables From Similar Manual Input.
May 26, 2007I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies View RelatedHow To Group Similar Column Name And Sum The Similar Column Name Together
Apr 10, 2008Hey Gurus,
I have a problem on getting the sql statement which will group similar column name and sum their number together(another column).
A million thanks in advance.
How To Use LIKE For 2 Or More Words
May 5, 2008Hello 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
Wildcards In Words
Apr 28, 2005Hi 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?
Searching Words
May 25, 2005hi 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
View 3 Replies View RelatedIgnored Words Nightmare!
Jan 10, 2006Hi 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
Number To Words
Jul 23, 2004hi
I want to know how convert number to words or words to number
for example. if i give input 100, i have to get the output "one hundred"
is there any built in function available. need solution immediately.
regards
nlakka
Searching For Whole Words?
Dec 21, 2005How 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]%'
Any ideas?
Goran
Numbers To Words
Aug 27, 2004Sir,
is there is any method to convert numeric ecpression to words ie 110 to "one humdred ten"
Pawandeep Singh
2-3 Words In Field Name
Dec 20, 2006Can 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?
View 1 Replies View RelatedJapanese Words
Jul 20, 2005Hi,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.
View 2 Replies View RelatedSearching With Ignored Words
Oct 24, 2007Hi,
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?
Select First 10 Words
Aug 28, 2007Can 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".
Thanks in advance!
Similar To PhpMyAdmin For MS Sql?
Apr 11, 2005Is there any program similar to phpMyAdmin for MS SQL servers?
Thanks in advance
Do I Need DatePart Or Similar
May 11, 2006I have a function that uses the following statement in it
SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref
WHERE (src_terrier.datadate = @dt_src_date) AND
(src_terrier.Areacode = @chr_div) AND
(src_centre_list.Portfolio_no = @vch_portfolio_no) AND
(src_centre_list.propcat = @vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)
The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows
src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.
How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.
Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.
Anybody confused by that , cause I am!
Regards
Is There St Similar To Directoryinfo In RS?
Apr 28, 2008hello there.
is there any case for getting the directory/file structure on the ReportServer?
something similar to "DirectoryInfo".
i'm using the webservice
best would be putting the url (e.g. "http://localhos/reportserver/reportgroup1") to a function and get at least an array of filenames in this folder back.
is there any way to solve this?
thanks in advance, tobias
Sys.sql_dependencies Or Similar?
Sep 11, 2007Hi!
I'm about to write some code that generate TSQLdoc.
For that I would like to show dependencies.
What will the best way to get actual info about
which procedures use this proc
which procedures or functions do this proc or func use?
Thanks
Searching For A List Of Words
Oct 21, 2005I 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
View 1 Replies View RelatedRegular Expression For Whole Words
Feb 8, 2006I am try to build a query which only matches whole words and so far I've got this.
Code:
SELECT *
FROM tblSearchWords
WHERE CorrectSpelling LIKE '%[^a-zA-Z0-9]blah[^a-zA-Z0-9]%'
This will return rows which contain the string 'blah' without any numeric or alphanumeric characters beside it. However it doesn't return the rows where 'blah' is either at the start or end of the string as it expects any character except a-zA-Z0-0.
Is there any way to accept string when there is nothing on either side as well?
Thanks,
Goran
Retrieve The 1st 15 Words Of A Field
Nov 29, 2004Hello,
i need a select statement that will retrieve only the 1st 15 words of a text field.
is it possible?
Thanks.
Unique Words Statement
Sep 22, 2004Hello all. I've been pulling my hair out for the last few weeks trying to come up with a statement that will do what I want. I'm hoping someone can lend some help.
Basically I have a table of articles with titles. I want to go thru the titles and find out what words show up the most. For example, if I had these two article titles in two records:
Microsoft develops new software for NASA
NASA blames software problem on Microsoft
I would get the following results - the word and the number of times it appears:
Microsoft 2
NASA 2
Software 2
The statement should ignore those words that only appear once. It would be nice to skip static words like the, and, a, etc.. (Or words 3 characters or less)
I have fulltext query enabled on the table which works great for searching, but not for what I want it to do.
Any suggestions? Thanks in advance.
Need Table Of Words/glossary/etc.
Mar 3, 2005Hi!
I have a client who wants to have a function to have a system create a validation system for users who register for a system. It would email them a registration code, which they want to be two or three random words strewn together.
So, I'm looking for a big table of words - random, glossary terms, etc. Does anyone have anything like this? (A flat file that I can import would be fine.)
Thanks!
How To Convert Numbers Into Words
Mar 1, 2006hi 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..
----
chakri
Counting No Of Words In A Column
Sep 13, 2006Hi,
for some reason, i had to write a function to count the number of words in a particular column in a table. (pl find the attachment). i would like to know whether there is any other mechanism with which we can count the number of words in a particular column.
for example, if the column data is,'This Is A Test', the function, will return 4.
pl suggest any other efficient strategies to accomplish this
thanks
Is There A Way To Have List Of NOT Words With Containstable?
Nov 6, 2007This works when @searchString is used in containstable (provided searchString has value)...
set @searchStringNoneOfWords = 'not(Airplane)'
SET @searchString = @searchString + ' AND ' + @searchStringNoneOfWords
This does NOT work when @searchString is used in containstable...
set @searchStringNoneOfWords = 'not(Airplane)'
SET @searchString = @searchStringNoneOfWords
I understand it is because the syntax is AND NOT, but what if I have a list of words that I do not want included? How do I start out with a NOT using containstable? It is kind of like Google's advanced search except that if you enter a word in the "without words" section with the other fields blank it would return everything under the sun except for things found with those words.
How To Convert Rupees Into Words
Feb 23, 2008hi ,
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