Searching For Accented Characters

Jul 7, 2000

Does anyone know how to search for accented characters ??

For example, we have a company called Nestlé and a company called Nestle but if someone types in Nestle then the Nestlé company doesn't show up ??

Any ideas would be greatly appreciated.

regards Paul

View 1 Replies


ADVERTISEMENT

Converting Accented To Non-Accented Characters

Feb 1, 2001

We need to compare data clients submit to us via the web to strings which we store which are comprised almost entirely of character codes less 127. If the data submitted to us has an accent, we're hosed. For example, if we get "¡¿Hasta Mañana, Sisqó!?", we need to map it to "!?Hasta Manana, Sisqo!?"
Is there an efficient way in T-SQL that will covert accented characters to their closest non-accented character?

View 1 Replies View Related

SQL Query Help - Accented Characters

Oct 10, 2007

Hello, I am trying to filter out any accented characters from a field in a select statement. I want to keep the accented version in the database, but have it convert when exporting.
Here is a sample of the php code that I used to use to accomplish this...

Code:


$shipfirstname = ereg_replace("[àâäåãáÂÄÀÅÃÁæÆ]", "a", $shipfirstname);
$shipfirstname = ereg_replace("[çÇ]", "c", $shipfirstname);
$shipfirstname = ereg_replace("[éèêëÉÊËÈ]", "e", $shipfirstname);
$shipfirstname = ereg_replace("[ïîìíÏÎÌÍ]", "i", $shipfirstname);
$shipfirstname = ereg_replace("[ñÑ]", "n", $shipfirstname);
$shipfirstname = ereg_replace("[öôóòõÓÔÖÒÕ]", "o", $shipfirstname);
$shipfirstname = ereg_replace("[ùûüúÜÛÙÚ]", "u", $shipfirstname);
$shipfirstname = ereg_replace("[ÿ]", "y", $shipfirstname);


So far, I have this sql code, but I will have to use a number of nested REPLACE statements to accomplish the same thing.

Code:

(SELECT REPLACE(ShipFirstName, 'à', 'a'))AS ShipFirstName,



Does anyone know an easier way to filter out these characters?

View 5 Replies View Related

Searching With Non-alphnumeric Characters

Mar 9, 2007

I am having a hard time finding information on how to search a nvarchar for non-alpha characters (stuff that is no a-z, A-Z, 0-9).  I was I could create a function that would go though each of the char in each nvarchar and check if it was "a-z,A-Z,0-9", and if it wasn't send back false, if it didn't find any, send back true.  Then in my query, I would but where function = false to just bring up the list that had the non-alphnumeric  characters.  My problem with that, i am not sure how to loop though each of the characters in the nvarchar.  Is there a better way? or should I continue with the function, if the function, how do I loop though each of the characters? 

View 8 Replies View Related

Searching With Non-alphabetic Characters

Jan 2, 2004

I need to create a Stored Procedure that will search for usernames in my database. I need it to return usernames where the first characters is ANYTHING BUT alphabetic characters (example: numerics, #, !, & etc.).

How can I do this in T-SQL?

View 2 Replies View Related

Searching For Return Characters...

May 21, 2001

I'm looking for a way to search for return characters (CHAR (13) or CHAR(10)) in VARCHAR fields. More specifically, either a way to remove them from the result set or replace them throughout the database.

Bonus Question:
Why can't users take a minute and fix their cut and paste into the address 1 field so that address 2 is in the right field (instead of on address 1 with returns in the middle of the data)?! Sheesh!

~Charles

View 1 Replies View Related

Searching For Unicode (japanese Characters)

Mar 8, 2004

I create my database table with a text field of nvarchar(), added some japanese kanji characters and so on. Everything works great, I can insert kanji and retrieve kanji and display them just fine from my c# application, however if I try to search for kanji using a WHERE = '' or a WHERE like '' clause, it doesn't score a match. Not even a direct one.

I'm on XP using a japanese locale with IME installed. The kanji shows up in Enterprise Manager correctly, it even shows in the query for the table, yet the WHERE clause won't record a hit. Changing the collation on the field to "Japanese" or "Japanese UNICODE" doesn't seem to have any effect.

Is there something I am missing here?

View 1 Replies View Related

Searching For Strings That Contain Characters Other Than Letters?

Aug 4, 2004

Hi,

I have a table in an Access db that stores information about speech files. One of the fields in this table is called "Text" and it contains the phrase spoken in that particular speechfile.

These phrases often have characters such as the "#" sign at the end to indicate what tone of voice is used.

I am trying to create a Search where users can enter the phrase they are looking for, and will be returned the file (or combination of files) that contain this phrase.

My problem is, when I try to search for a string of text that includes the "#" I get 0 results everytime.

An example of what I am doing is this:

SELECT Speechfiles.Name FROM Speechfiles
WHERE Speechfiles.Text LIKE 'aero#'


It works fine for 'aero' or '*aero*' but whenever I try to add a character that is not a letter, it won't work.

If anyone has any ideas, I would REALLY appreciate it!!! I am completely at a loss.

Thanks so much....

View 1 Replies View Related

T-SQL (SS2K8) :: Searching For Allowed Characters From Table Using Function?

Aug 10, 2015

I want to create a function that searches for allowed characters within a table range (that contains the allowed characters) and replace any characters outside this range with a space.

For example -

'Bill123?', 'Jones12.z-'
'John&12/', 'QWERT123&4'

Wanted results – the single quotes are there to show the space for the replaced characters.

'Bill123 '
'Jones12.z '
'John&12 '
'QWERT123 4'

Example SQL data

CREATE TABLE [Common].[AllowedCharacters] (
[Character] [varchar](1) NOT NULL,
[Replacement] [varchar](10) NULL,
[AlwaysInclude] [bit] NOT NULL)
GO
SET ANSI_PADDING OFF

[code]....

The function will wrap around the column names and I know it can be done without a table validate the characters but it must be done this way.

View 9 Replies View Related

Searching A String With Asian Characters (double Byte)

Jun 14, 2007

My problem is that i can't search a field that contains Asian characters (Korean in this case).



The table user_Access_tab have 2 keys:

access_id nVarchar(50)

user_id nVarchar(50)



The sql query below is sent through a oledbcommand to a sql server 2005 database.



"select access_id, access_right from user_Access_tab where user_id ='HQ001kimjo012007-05-07 ì˜¤ì „ 11:50:323401'"



It doesn't show any hits even thogh i know there is a number of matching records.



The question doesn't generate an answer in SQL server manager studio eigher.



If i change the datatype on user_id to Varchar(50) the id is presented (in the database) as:

HQ001kimjo012007-05-07 ?? 11:50:323401



Then the question works, but why doesn't it work with nVarChar(50)?



Regards Martin Jonsson

View 1 Replies View Related

Pattern Matching - Searching For Numeric Or Alpha Or Alpha-Numeric Characters In A String

Aug 18, 2006

Hi,

I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.





Wildcard
Meaning



%


Any string of zero or more characters.



_


Any single character.



[ ]


Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).






Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).

Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.

In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:

WHERE
Gift_Date NOT LIKE "####*"

After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:

WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'

using single quotes and the % wildcard instead of Access' double quotes and * wildcard.

Just putting this out there for anybody else that is new to SQL, like me.

Regards,

Patrick Briggs,
Pasadena, CA






View 1 Replies View Related

How Can I Store Over 16000 Characters To Sql Table Field With Language Specific Characters?

Feb 19, 2008

In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
 
Thanks
 
 

View 3 Replies View Related

Separate Lowercase Characters From Uppercase Characters

Mar 5, 2008


Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?

Hellen

View 1 Replies View Related

Iliminating Characters From Set Of Integers And Characters

Jul 19, 2006

Good day experts,

I wonder if i got an answer for this.
How can i iliminate a letters from a set of integers and characters using a SQL Statement

for ex:

ABC9800468F

is that possible?
is there a function that i can use to iliminate them?

View 3 Replies View Related

Transact SQL :: Replace Column Value From ASCII Characters To Non ASCII Characters In Table?

Oct 22, 2015

I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.

Note – values in column must be same

View 10 Replies View Related

Searching For ._

Apr 24, 2008

I am trying to find all the email addresses with a " ._"    I use '%._%' but it returns all records.  What is the correct syntax? Also, is there a way to search for a field where the underscore is followed by a single alpha letter and then another underscore? like bla_A_bla or bla_Z_bla.thanksMilton
SELECT DISTINCT fname, lname, inet
FROM ocadbo.notes
where inet like '%._%'

View 6 Replies View Related

Need Some Help In Searching

May 8, 2004

Dear ASP.NET

How can I find records that contain a STRING from some (more than one) other fields ?

for example, I have:

Name_First = "aaa"
Name_Middle = "bbb"
Name_Last = "ccc"

Key_Words = "aaa,bbb,ccc"
(includes all values - comma separated)

How can I do the SELECT so that when I search for "bbb" on Key_Words I will get my record ?

Should I use "LIKE %aaa%" or something like this ?
(should I keep the comma separators ?)


Thanks in advance, Yovav.

View 3 Replies View Related

Searching With LIKE

Feb 13, 2006

Let say a user wants to search for the name Joe Soap

I have two column's in my table, firstname and lastname

So if I do:


Code:


SELECT firstname, lastname FROM table WHERE firstname LIKE '%Joe Soap%' OR lastname LIKE '%Joe Soap%'



it returns nothing! So do I have to split the string Joe Soap or something ?

View 1 Replies View Related

Searching...

Nov 28, 2004

my app use a registration table

StudReg
(
stName varchar(30),
stDOB smalldatetime,
stGuardianName varchar(30),
stRegDt smalldatetime,
stRegNo bigint,
courseId smallint
)


the application registers the student details to a course.
each student gets a new registration no during registration.

the app should identify repeaters to a particular course by checking another
table RegHistory, which stores the details of student registrations for the previous 5 years.

RegHistory
(
stName varchar(30),
stDOB smalldatetime,
stGuardianName varchar(30),
stPrevRegDt smalldatetime,
stPrevRegNo bigint,
courseId smallint
)


the application must search the RegHistory table and list out those students who are the repeaters.


the sample entries in the two tables are as follows

StudReg
stName stDOB stGuardianName stRegDt stRegNo courseid
-------------------------------------------------------------------------
abc 01/01/1979 def 20/11/2004 12345 1
def 01/01/1976 xyz 20/11/2004 12346 1
... ..... ... ...... .... ...

mno 24/18/1976 pqr 20/11/2004 12400 1



RegHistory

stName stDOB stGuardianName stPrevRegDt stPrevRegNo courseId
abc 01/01/1979 def 20/11/2001 2345 1
ghi 01/01/1976 xyz 20/11/2001 2346 1
... ..... ... ...... .... ...

dfg 24/18/1976 pqr 20/11/2001 2400 1


to determine whether a student is a repeater or not, we have to search for an exact match in RegHistory table (where the student name, guardian name and date of birth in both tables match with the corresponding entries in Registration table).


here is my question,

if there are 100,000 students registering in each academic year, we will have
500,000 records in RegHistory Table and 100,000 records in studReg table

if i start searching for a repeater, i guess i will have to loop through all records in studReg, for an exact match in RegHistory, which wil be a time consuming process.

is there any other options to search for repeaters ?

pl discuss

View 11 Replies View Related

Searching

Mar 16, 2004

hi all,
I need help in selecting records from a table based on the given search criteria.
spec:
select * from table where col1='x' and col2='y'... and col6='q'
i may give any combination of column values.
I mean I can't provide 6 values in 'where' condition all the time i submit query.
help me with a stored proc which has 6 input parameters for the 6 columns.

View 9 Replies View Related

Searching

Dec 11, 2006

Maybe a dumb question.. but is there a way to search all tables in a database for a particular word or phrase?

View 4 Replies View Related

Searching For Add-In

Feb 18, 2008

I am searching for an add-in to ssms which lets me choose which server to deploy my code.

For example, I have a complete statement with CREATE PROCEDURE and all code in my query window.

Now I want to run/deploy this code to many servers at once.
Instead of having to reconnect to all server (5), I want an add-in which have checkboxes for me to select.

Anyone heard of this?


E 12°55'05.25"
N 56°04'39.16"

View 6 Replies View Related

XML And Searching

Jul 23, 2005

Hi,I have come to point in my db design where I'm trying to figure whichis the best approach in making it generic (does this really matter?!?)Senario - I have a table called JOBS and this table contains fieldsuch as JobTitle, JobDescription, Salary etcI want to add to this table other attributes which are specific to acertain Job Industries.Solution - Add a join table for each type of industry containingattributes (db is now not generic) OR add a new table with a IndustryType field and a XML field containing the industry specificattributes.If I go the XML way will this just make it complex and slow to query?If not, what is the best way to query an XML field?Thanks,Jack

View 2 Replies View Related

Searching

Jul 23, 2005

I am having a problem with seaching my tables. On my asp.net page ihave 3 text boxes. One for an ID and NAME and ADDRESS. I want to beable to search a table by using all or any combination of the 3 tosearch. But i cant get it right. i am using c# and sql server.any help ideas would be a great helpthanks in advanceructions

View 1 Replies View Related

Searching With '

May 26, 2008

How do we make a search in dbase where in the data you are searching has a " ' " for example Int'l Airport or other wildcards


Regards

View 4 Replies View Related

Searching A Databse

Aug 31, 2006

i'm making a web page for a clinic.it needs to be able to search for patients by first name, surname, date of birth and patient number.i'm using visual web developer and i have my database, my data source and GridView grid.i want  4 text boxes for my first name, surname etc. when u click enter on any of them i want to retrieve all their data and display it in the gridview.at the moment i have  one text box on the web page and through the "Configure data source" option on the grid view i can retrieve the specified data but for only this one item, e.g. SELECT * FROM [Patients] WHERE ([DOB] = @DOB). if i add another text box to my web page, and don't do anything to it, the query wont run. if i add and "AND" statement to the query, e.g. SELECT * FROM [Patients] WHERE (([DOB] = @DOB) AND ([FirstName] = @FirstName)), again it won'r run or return and data. any ideas on what i can do or where i'm going wrong. thanks 

View 1 Replies View Related

Best Searching Criteria

Sep 30, 2007

I have a table
 GO
 CREATE TABLE [dbo].[Speech] (  [SpeechId] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkSpeech_SpeechId PRIMARY KEY,  [UniqueName] [varchar](52) NOT NULL,  [NativeName] [nvarchar](52) NOT NULL,  [Place] [nvarchar](52) NOT NULL,  [Type] [smallint] NOT NULL,  [LanguageId] [char](2) NOT NULL CONSTRAINT FkSpeech_LanguageId FOREIGN KEY (LanguageId) REFERENCES Language(LanguageId) ON UPDATE CASCADE ON DELETE CASCADE,  [SpeakerId] [int] NOT NULL CONSTRAINT FkSpeech_SpeakerId FOREIGN KEY (SpeakerId) REFERENCES Speaker(SpeakerId) ON DELETE CASCADE,  [IsFavorite] [bit] NOT NULL,  [IsVisible] [bit] NOT NULL,  [CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(),  [ModifiedDate] [datetime] NULL )
Now I want to search the Table Speech
Sometimes by : SpeechIdSometimes by : SpeakerIdSometimes by : LanguageIdSometimes by : SpeechId And LanguageIdSometimes by : SpeakerId And LanguageId
All can have conditions with IsVisible, IsFavorite and Type columns.
for example
I need all Speeches withany particular SpeakerId and LanguageIdwith IsVisible equals to trueand IsFvaorite No Matterand Type equals to Audio
For these type of queries I think the solution is
GO
 CREATE PROCEDURE [dbo].[sprocGetSpeech]
  @speechId int = NULL,  @uniqueName varchar(52) = NULL,  @nativeName nvarchar(52) = NULL,  @place nvarchar(52) = NULL,  @type smallint = NULL,  @languageId char(2) = NULL,  @speakerId int = NULL,  @isFavorite bit = NULL,  @isVisible bit = NULL
 AS
  SELECT   SpeechId,   UniqueName,   NativeName,   Place,   Type,   LanguageId,   SpeakerId,   IsFavorite,   IsVisible,   CreatedDate,   ModifiedDate  FROM   Speech  WHERE   SpeechId = @speechId   AND UniqueName = CASE WHEN @uniqueName IS NULL THEN [UniqueName] ELSE @uniqueName END   AND NativeName = CASE WHEN @nativeName IS NULL THEN [NativeName] ELSE @NativeName END   AND Place = CASE WHEN @place IS NULL THEN [Place] ELSE @place END   AND Type = CASE WHEN @type IS NULL THEN [Type] ELSE @type END   AND LanguageId = CASE WHEN @languageId IS NULL THEN [LanguageId] ELSE @languageId END   AND SpeakerId = CASE WHEN @speakerId IS NULL THEN [SpeakerId] ELSE @speakerId END   AND IsFavorite = CASE WHEN @isFavorite IS NULL THEN [IsFavorite] ELSE @isFavorite END   AND IsVisible = CASE WHEN @isVisible IS NULL THEN [IsVisible] ELSE @isVisible END
Can anyone tell me?
Is it right way to do?Do you have any better solution?If my solution is better then Is there any performance loss with that query?

View 1 Replies View Related

Good Searching

Jan 7, 2008

 hello all..i have make a searching, but is not good. my code like that:Public Class getall Public Function getitem(ByVal id As String) As DataSet        Dim con As SqlConnection = New SqlConnection("Data Source=BOYsqlexpress;Initial Catalog=GAMES;User ID=ha;Password=a")        Dim ds As New DataSet()          Dim adapter As New SqlDataAdapter("select * from [item] where name like '%" & id & "%'", con)        Try            con.Open()            adapter.Fill(ds, "user")            Return ds        Catch ex As Exception            Console.Write(ex.Message)        Finally            con.Close()            con = Nothing        End Try        ' Next        Return ds    End Functionand class  my item in database is containning  dragon ball 3, counter strikeif i insert dragon, it can display dragon ball 3.but if i insert dragon 3, it not display dragon ball 3.it should display dragon ball 3 .how should i change my code?thx... 

View 1 Replies View Related

Problem Searching Value

Apr 18, 2008

hello everyone
 i need  C# code in wh we sent "UserID" to table named "Users"
and if such "UserID" exists in "User" table we get his name from the table else we got message that no such record exists.
i have tried to write that code ,it works only if the value exists . in case of no value an exception is thrown.
please do sent me that c# code.
thanks for your consideration.

View 3 Replies View Related

Searching In Tables

Dec 8, 2004

Hi All,


Just wanted to run this idea past u all before I have a go at it.

I have two tables A & B that are similar to the below


Table A
Name1 Name2 Name3
Tom Bill John
Gary Harry Eric


TableB
Name1 Name2 Name3
John Bill Tom
Tom Eric john
Leslie Philip Colin


What I wanted to do is see if the the records from tableA row 1 exist in tableB

As you can see they can appear in any order, partially or not at all.

What I propose to do is take tableA Name1 and see if it matches tableb row1 name 1 OR name 2 OR name 3 and if I find a match use a variable to assign the the value 1 (so I can then see if the match is full (score three) partial socre 1 and two or not at all (0)

Then I will need do the same for tableA row 2

Then goto row 2 of table a and start again.

Does this make sense? Are there beter ways of doing this.

I am using SQL server to do the searching....?

All comments appreciated and welcome.

rgs

Tonuy

View 4 Replies View Related

Searching Words

May 25, 2005

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

View 3 Replies View Related

Text Searching

Jun 29, 2005

Hi again!

I have a products table with product attributes in a second table,
together they describe a full product.  I have a product title, a
list of providers, description text, and keywords.  I would like
to do a search across these fields, and so far my research has shown
that the Full Text Search component of SQL Server is the way to
go.  However, I am not sure this will be possible based on what is
installed on the hosted server, so I am wondering if there is a unique,
cool way of doing this without Full Text Search?

Thanks,

jr.

View 5 Replies View Related

Searching Database

Oct 11, 2005

i currently have a function and a storedpro in my sql database they are:CREATE PROCEDURE SearchCatalog (@PageNumber tinyint,@ProductsOnPage tinyint,@HowManyResults smallint OUTPUT,@AllWords bit,@Word1 varchar(15) = NULL,@Word2 varchar(15) = NULL,@Word3 varchar(15) = NULL,@Word4 varchar(15) = NULL,@Word5 varchar(15) = NULL)AS
/* Create the temporary table that will contain the search results */CREATE TABLE #SearchedProducts(RowNumber SMALLINT NOT NULL IDENTITY(1,1), ProductID INT, Name VARCHAR(50), Description VARCHAR(1000), Price MONEY, ImagePath VARCHAR(50), Rank INT, ImageALT VARCHAR(100), Artist VARCHAR(50))
/* Populate #SearchedProducts for an any-words search */IF @AllWords = 0    INSERT INTO #SearchedProducts           (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank)   SELECT Product.ProductID, Product.Name, Product.Description,           Product.Price, Product.ImagePath, Product.ImageALT, Artist.ArtistName,          3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)+          3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)+          3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)+          3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)+          3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)           AS TotalRank   FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID   ORDER BY TotalRank DESC  
/* Populate #SearchedProducts for an all-words search */IF @AllWords = 1    INSERT INTO #SearchedProducts           (ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, Rank)   SELECT Product.ProductID, Product.Name, Product.Description, Product.Price, Product.ImagePath,   Product.ImageALT, Artist.ArtistName,          (3*dbo.WordCount(@Word1, Name)+dbo.WordCount(@Word1, Description)) *          CASE              WHEN @Word2 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word2, Name)+dbo.WordCount(@Word2, Description)          END *          CASE              WHEN @Word3 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word3, Name)+dbo.WordCount(@Word3, Description)          END *          CASE              WHEN @Word4 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word4, Name)+dbo.WordCount(@Word4, Description)          END *          CASE              WHEN @Word5 IS NULL THEN 1              ELSE 3*dbo.WordCount(@Word5, Name)+dbo.WordCount(@Word5, Description)          END          AS TotalRank   FROM Product INNER JOIN (Artist INNER JOIN AlbumSingleDetails ON Artist.ArtistID = AlbumSingleDetails.ArtistID) ON Product.ProductID = AlbumSingleDetails.ProductID   ORDER BY TotalRank DESC
/* Save the number of searched products in an output variable */SELECT @HowManyResults=COUNT(*) FROM #SearchedProducts WHERE Rank>0
/* Send back the requested products */SELECT ProductID, Name, Description, Price, ImagePath, ImageALT, Artist, RankFROM #SearchedProductsWHERE Rank > 0  AND RowNumber BETWEEN (@PageNumber-1) * @ProductsOnPage + 1                     AND @PageNumber * @ProductsOnPageORDER BY Rank DESCand:CREATE FUNCTION dbo.WordCount(@Word VARCHAR(20),@Phrase VARCHAR(1000))RETURNS SMALLINTASBEGIN
/* If @Word or @Phrase is NULL the function returns 0 */IF @Word IS NULL OR @Phrase IS NULL RETURN 0
/* Calculate and store the SOUNDEX value of the word */DECLARE @SoundexWord CHAR(4)SELECT @SoundexWord = SOUNDEX(@Word)
/* Eliminate bogus characters from phrase */SELECT @Phrase = REPLACE(@Phrase, ',', ' ')SELECT @Phrase = REPLACE(@Phrase, '.', ' ')SELECT @Phrase = REPLACE(@Phrase, '!', ' ')SELECT @Phrase = REPLACE(@Phrase, '?', ' ')SELECT @Phrase = REPLACE(@Phrase, ';', ' ')SELECT @Phrase = REPLACE(@Phrase, '-', ' ')
/* Necesdbory because LEN doesn't calculate trailing spaces */SELECT @Phrase = RTRIM(@Phrase)
/* Check every word in the phrase */DECLARE @NextSpacePos SMALLINTDECLARE @ExtractedWord VARCHAR(20)DECLARE @Matches SMALLINT
SELECT @Matches = 0
WHILE LEN(@Phrase)>0  BEGIN     SELECT @NextSpacePos = CHARINDEX(' ', @Phrase)     IF @NextSpacePos = 0       BEGIN         SELECT @ExtractedWord = @Phrase         SELECT @Phrase=''       END     ELSE       BEGIN         SELECT @ExtractedWord = LEFT(@Phrase, @NextSpacePos-1)         SELECT @Phrase = RIGHT(@Phrase, LEN(@Phrase)-@NextSpacePos)       END
     IF @SoundexWord = SOUNDEX(@ExtractedWord)       SELECT @Matches = @Matches + 1  END
/* Return the number of occurences of @Word in @Phrase */RETURN @MatchesENDmy database has many table but product is linkinked to albumsingledetails with productid in the albumsingledetails table, the the albumsingledetails table has the artistid in it which links to the artist table. I have tried searching for an artist but it does not find them!! can anyone see where i have gone wrong?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved