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.
Hi everybody! I have a table like this: Create table t ( id int texto varchar(400) ) And this table have around 1000000 rows. I want to be enable to make the next query in few seconds:
Select * From T where texto like %palabra%
Some idea how can I replace the like for something else. Something importan, I can't use full-index.
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?
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.).
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!
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.
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.
I am trying to write a function to compare the characters between 2 strings and eliminate the similarities to be able to return at the end the number of differences between them.
Having in mind i need the bigger number of differences to be returned also if a character is repeated in one of the 2 words it will be eliminated once because it exist only one time in other string.
I will give an example below to be more clear
--Start declare @string1 as varchar(50)='imos' declare @string2 as varchar(50)='nasos'; WITH n (n) AS ( SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
[Code] ....
The differences in first string from second one are 2 (i,m) while the differences in second string from first one are 3(nas). So the function should return 3 in previous example.
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?
I'm working on a stored procedure that works fine. I just want to make it possible for the user to be able to have a drop down list in reporting services to display the "question codes" grouped by whatever the first two digits are. for example.
VT01 VT02 VT03 VN01 VN02 VN03 ST01 ST02 ST03
instead of listing everything, i want the viewers to see this VT VN ST or an alias for each of these like this:
Vet Tasks Vet National Survey Tasks Survey National
any ideas, here's my current code, which is pullin up anything with the added substring part
Code Snippet ALTER PROCEDURE [dbo].[Testing_Questions] (@Region_Key int=null,@QuestionCode char(5)) AS BEGIN
SELECT dbo.Qry_Questions.Territory, dbo.Qry_Questions.SalesResponsible, dbo.Qry_Questions.Customer, dbo.Qry_Questions.Date, dbo.Qry_Questions.StoreName, dbo.Qry_Questions.PostCode, dbo.Qry_Questions.Address2, dbo.Qry_Questions.[Question Code], dbo.Qry_Questions.Question, dbo.Qry_Questions.[Response Type], dbo.Qry_Questions.response, dbo.Qry_Questions.sales_person_code, dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Sales_Group.Region FROM dbo.Qry_Questions INNER JOIN dbo.Qry_Sales_Group ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code WHERE REGION_KEY=@Region_Key AND SUBSTRING(dbo.Qry_Questions.[Question Code],0,3)=@QuestionCode
I would like to perform a procedure which takes a string of numbers and searches for strings in a table which include all the given numbers in any particular order. problem is i can only return strings which include 'any' of the letters in the search string and not 'all'.
this is how i perform it so far:
@IncludeNumbers VARCHAR(50)
AS BEGIN
SELECT Phone AS [Telephone Number]
FROM tbl_Person AS p
WHERE (p.Phone LIKE '%[' + @IncludeNumbers + ']%')
END
(ive removed some unecessary bits but this shows the basics)
As you can see currently it will return any phone numbers which contain any numbers given in the includeNumbers string, I would only like it to return phone numbers which contain all the numbers given in the includeNumbers string. is there any way to make it search for strings which include 'all' numbers given?
I'm writing to ask if anyone knows whether or not MS SQL server stores in any system tables the association between a database and the drive letter/directory path where its corresponding MDF/LDF files are located.
I have a test server that needs the drive letters changed for the data, log and backup drives. Is there a way to make this change without reinstalling SQL Server?
I would like to design a 'mail merge' type of letter, whereby some of the paragraphs will be conditional on parameters. Can anyone direct me to example of mail merger letters using reporting services?
Hi. I'm looking for tutorials on how to use SSRS 2005 to generate letters in Word or PDF format from a database. We need to send letters with our company logo and some boilerplate text to a set of companies based on a SQL Server query. So the letter will look something like this, with the red areas repeating from the dataset,
[logo goes here]
123 Any St. AnyCity, CA 90210
[today's date]
Dear [name from database],
Some boilerplate text goes here. Your Account [account number from database] is past due.
Ho can I convert first letters of a string to Upper Case (i.e. UNITED KINGDOM - Untited Kingdom). I have country names table which has all entries in uper case. This makes a select box very larg and unproportional. Thanks in advance for the help.
Can you restore the master database, running on windows server 2000, sqlserver 2000 from a backup that was taken from c: to another serverrestoring to d:?*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have a field (varchar) in a list that contains numbers and letters. I want to sort this table but I have only two functions that will work to convert the values:
Val The Val function sorts the numbers in the string, but the letters are not sorted
CStr The CStr function sorts the letters, but the numbers are not sorted
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?
First I'm a big NEWBIE, so it'd be great if you can provide a little explanation as to how this should be done...
Here's what I want to do, but have no idea how to approach it.
I have a table with Quotes (table Quote) in them (for a Sale's Team). Each has a quote number (qtQN) and this number is sequencial but sometimes revised where a letter is added at the end. Like so:
The first 3 digit are company codes and pretty unimportant to this problem. As you can see, 111q0002 has three versions. A, B, and no letter. The most up to date is B. So in this list I want to list only the most up to date quotes. So the resulting list would be:
111q0001 111q0002B 222q0005
Get it? Good, cuz I have no idea how to query that... any help at all is appreciated!
I'm using MS SQL Server 2k and scripting with ASP 3.0
