|
Not sure if there is any built-in function from MSSQL. But you can write your own function -->
select UPPER(SUBSTRING(colName, 1, 1)) + LOWER(SUBSTRING(colName, 2, LEN(colName))) from tblTable
Eliz.K
www.oin1.com
|
|
|
|
|
this does only first letter of of the text to uppercase.
not first letter of all words in the text
there is no in built function for this. need to write a udf for this.
Regards
KP
|
|
|
|
|
Oh ya... sorry for my mistake.
To look through every word will affect the performace. I have a way which is not very clever, but should be workable.
Write a user definied function, where you replace ' a' to ' A', ' b' to ' B'.. and so on.
Sorry...
Eliz.K
www.oin1.com
|
|
|
|
|
Hi guys
I am trying to execute a dts package which has a text file as it's source (comma delimited with .txt extension) and then loads it into a table in sql server 2000. When I execute it, it fails on the first datarow it reads on the second field, the first field's value it reads.
THis is the error message:
"The number of failing rows exceeds the maximum specified.
Readfile transform 'DTSTransformation_2', source column 'MSISDN' (DBTyPE_STR), destination column 'PhoneNo'
(DBTYPE_STR): File '0842473397' not found."
funny thing is that '0842473397' is not a file, it's the second value in the first data row in the text source file.
Baffled, Plz Help.
Kiefie
Jack of all trades, master of some.
|
|
|
|
|
Nevermind I got it, I should've used 'copy column' and not 'read file' transformation.
Kiefie
The man with a plan.
|
|
|
|
|
Hi,
Can any one tell me how to connect with SQl using OleDb or Odbc.
Suppose the database name is TestData.mdf. Now what would be ths exact connection string?.
|
|
|
|
|
|
Dang, you beat me to it 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi,
When I run the following query I get results:
SELECT *
FROM tblMyFullTextSearchTable
WHERE FREETEXT(fldMyTitle, 'religion');
..but when I run the following query, I get no results:
SELECT *
FROM tblMyFullTextSearchTable
WHERE FREETEXT(fldMyTitle, 'the');
I know for a fact there are titles with the word the in it.
Please help.
Regards
ma se
|
|
|
|
|
Try CONTAINS.
SELECT *
FROM tblMyFullTextSearchTable
WHERE CONTAINS(fldMyTitle, 'the');
|
|
|
|
|
This doesn't work either.
SELECT *
FROM tblMyFullTextSearchTable
WHERE CONTAINS(fldMyTitle, ' "the" OR "and" ');
What I am trying to achieve is what ever words are passed through, I need to search on it. So if I were to pass through the word "a" or "an" or "the" I need it to bring back data that contains these words.
Regards
ma se
|
|
|
|
|
hi
i Hope this mail will help u little bit.
FREETEXT:
Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally "word-breaks" the freetext_string into a number of search terms and assigns each term a weight and then finds the matches
The following example searches for all documents containing the words related to vital, safety, components.
SELECT Title
FROM Production.Document
WHERE FREETEXT (Document, 'vital safety components' );
I think contions will give u r result
contains can use in this cases.
A word or phrase.
The prefix of a word or phrase.
A word near another word.
A word inflectionally generated from another (for example, the word "drive" is the inflectional stem of drives, drove, driving, and driven).
A word that has a higher designated weighting than another word.
Syntax
regards,
.Net professional
|
|
|
|
|
Thanks, but I have another scenario:
I am using SQL Server 2005, and I have set up a full text search on my products table.
I am using ASP.NET 1.1. In my search.aspx page I have 4 textboxes used in the search. But only one of the textboxes are required for the search. I'm not sure hot to construct my SELECT statement based on the given criteria because some of the textboxes can be blank.
I have a title textbox where the user can type a couple of words that he wants to find in the book's title. How do I specify this in the SELECT clause, as it will not be an exact match, but a combination of words in the title.
I would appreciated it if someone could get back to me.
Regards
ma se
|
|
|
|
|
???
SELECT Title
FROM Production.Document
WHERE Document LIKE '%vital safety components%'
|
|
|
|
|
Hi Guru's,
I made a basic search filtering query, it searches for the predefine (default) values when user does not place any values on search fields, my search query works fine but not until having the sort option, below is my query and my db structure:
TABLE:
ListingID int
HLCN varchar(6)
HousePrice money
Bathrooms float
Bedrooms int
HomeLocation varchar(50)
ContactName varchar(50)
EmailContact varchar(50)
PhoneContact varchar(50)
Address varchar(100)
SquareFeet varchar(50)
YearBuilt varchar(50)
LotSize varchar(50)
Garage varchar(50)
Notes varchar(255)
QUERY:
declare @sort nvarchar(50)
set @sort ='HomeLocation'
Select
listingID,
HLCN,
HousePrice,
Bedrooms,
Bathrooms,
HomeLocation,
ContactName
from
listings
where
houseprice between 0 and 100000000
and
bathrooms >= 0
and
bedrooms >= 0
and
homelocation like '%' + '' + '%'
order by
case @sort
when 'HomeLocation' then HomeLocation
when 'HousePrice' then HousePrice
end
when I sort by HousePrice it return correct resultm but when I sort by Homelocation it returned
Cannot convert a char value to money. The char value has incorrect syntax. I'm wondering whey It's causing char-to- money conversion error when I'm not passing char values and also this only happens when I'm using HomeLocation as sort.
Please advice,
Thanks
Dom
|
|
|
|
|
I believe it is because the HomeLocation is VARCHAR type while the HomePrice is MONEY type. I try the following and it works (LoginID is VARCHAR, UserID is INT):
DECLARE @sort NVARCHAR(50)
SET @sort ='LoginID'
SELECT * FROM tblUser
ORDER BY
CASE @sort
WHEN 'LoginID' THEN CONVERT(VARCHAR, LoginID)
WHEN 'UserID' THEN CONVERT(VARCHAR, UserID)
END
Eliz.K
www.oin1.com
|
|
|
|
|
Works great Eliz
Thank you
Dom
|
|
|
|
|
Works great Eliz
Thank you
Dom
|
|
|
|
|
Hi,
I am using SQL Server 2005, and I have set up a full text search on my products table.
I am using ASP.NET 1.1. In my search.aspx page I have 4 textboxes used in the search. But only one of the textboxes are required for the search. I'm not sure hot to construct my SELECT statement based on the given criteria because some of the textboxes can be blank.
I have a title textbox where the user can type a couple of words that he wants to find in the book's title. How do I specify this in the SELECT clause, as it will not be an exact match, but a combination of words in the title.
I would appreciated it if someone could get back to me.
Regards
ma se
|
|
|
|
|
need suggestions to this problem
Data is like this(WHEN RETURNING FROM DIFFERENT TABLES)
column A column B
7001 NULL
7002A A
7002AB AB
7003VA VA
7003AV AV
7004ABC ABC
7005XYZ XYZ
NEED TO CONVERT TO LOOK LIKE THIS FOR FUTURE COMBINATIONS
column A column B
7001 NULL
7002A A
7002AB A
7002AB B
7003VA V
7003VA A
7003AV A
7003AV V
7004ABC A
7004ABC B
7004ABC C
ANY HELP WILL BE HELPFUL
|
|
|
|
|
Hello everyone,
I am working on an Windows Application which requires to access a remote database. I would like to be able to obtain the size of the tables before they are used to populate the DataTable in the Windows Application so, the data tranmission is displayed to the user.
I am using "mysql_fetch_array(SHOW TABLE STATUS FROM MyDatabase LIKE 'MyTableName')" command to get the Data_Length of the table. But I am unable to get this working. Can someone be kind enough to tell me how I can get the Table Size (Byte) from Windows Application (C#)?
Thank you very much and have a great day
Khoramdin
|
|
|
|
|
|
I want to display all data from my table except first three colunms. Is there any way to use "select * " query to see all colunms except first three? I'll greatly appreciate any help.
Thanks
|
|
|
|
|
You can do it in front-end programming languages really easily (in .NET you would iterate through the relevent columns returned in a DataReader).
It is really difficult to do within the database - you would have to dynamically construct a select statement based upon the table's column meta data  . Generally, doing "select * from xxxx" or doing an insert without explicitely specifying each column is considered to be bad practice for production code.
|
|
|
|
|
Create a view on the table ignoring or selecting only those columns required
then use "SELECT *" on the query
Regards
KP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
