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

Problem Searching Strings

May 14, 2001

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.

Thank you.

Zoraya

View 1 Replies View Related

Searching Strings For Specific Chars ?

Nov 16, 2007

Hi if i wanna search a certain column for a specific character

so it could be '&' occuring anywhere in the string
can i use the like operator ?

so have something like

SELECT name from table
where name like '%&%'

View 7 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 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 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

How Do I INSERT Strings With Unsual Characters

Mar 2, 2001

The Query:
INSERT INTO Table1
VALUE('this doesn't work')

How can I Insert strings like the one above, into my database?

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

SQL Server 2012 :: Compare Characters Between 2 Strings And Eliminate Similarities

Oct 13, 2015

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.

View 4 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

Concatenate Strings After Assigning Text In Place Of Bit Strings

Feb 19, 2007

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:

select case new_accountant = 1 then 'acct/' end +

case new_advisor = 1 then 'adv/' end +

case new_attorney = 1 then 'atty/' end as String

from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

View 2 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

CAPITAL LETTERS

Sep 6, 2007

Any option on sql server to have the reserved words converted into CAPITAL when I type it in. Like "SELECT"

=============================
http://www.sqlserverstudy.com

View 8 Replies View Related

Group Everything With The Same First Two Letters

Aug 17, 2007

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

END
SET NOCOUNT OFF

View 1 Replies View Related

WHERE Includes All Letters

Apr 18, 2006

Hi

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?

thanks

Robert

View 7 Replies View Related

Query Column With Letters

Sep 22, 2004

I have a database with a zip code column. I want to flag all rows that have letters in that column (a-z). How could I construct the where clause?

Is there a better alternative to:
WHERE ZipCode LIKE '%a%' OR ZipCode LIKE '%b%' ...

View 5 Replies View Related

Drive Letters And MDF/LDF Files...

Apr 18, 2007

All,

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.

Thanks,

Isaac

View 4 Replies View Related

Changing Drive Letters

Oct 9, 2007

All,

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?

Future guru in the making.

View 13 Replies View Related

Dynamic Form Letters

Aug 6, 2006

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?

thanks

ken

View 1 Replies View Related

Generating Letters In SSRS

Aug 21, 2007

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.

Sincerely,

[scanned signature goes here]

Somebody

View 2 Replies View Related

How To Apply Capital Letters But Only The First One?

Nov 20, 2007

Hi everyone,

As source on a Sql25k table I've got this (often):

XXXXX ZZZZZZZ YYYYYYYY

And, as a result I'd need this (plain file):

Xxxxx Zzzzzz Yyyyy

Ok guys, with VbScript inside a DTS it's very easy, building a loop and then playing with Ucase, Left functions stuff and so on...

How could I do this using a DTSX package?? Any specialized task?

Thanks indeed for your help,

View 29 Replies View Related

Help On Extracting Only With Three Capital Letters From The Field

Feb 12, 2008

Hi Everybody

I have this situation where I need to extract values from a field that satisfies this criteria...

ONLY THREE LETTERS AND
ALL IN CAPITAL LETTERS

thanks

View 14 Replies View Related

First Letters Of A String To Upper Case

Apr 23, 2002

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.

Php95saj

View 14 Replies View Related

Restoring The Master To Different Drive Letters

Jul 20, 2005

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!

View 1 Replies View Related

.NET Framework :: Generating PDF Letters From Server

Jul 9, 2015

Generating different types of letter (PDF formats) from SQL server?

View 3 Replies View Related

Sorting A Table With Numbers And Letters

Jan 25, 2007

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

How can I sort the numbers and letters?

View 4 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

Query Help, Get Count Of Names Having Distinct Letters

May 8, 2007

here is a query

Code:

select count(d) from (select distinct left(name,9) as d from bscs where region like 'n1' and name like 'Alc_%')

what i am trying to do is to get count of the names, having distinct letters in left 9 positions and some conditions too. How to do it?

View 5 Replies View Related

SELECT Only The Newest, But Based On Revised Letters

Feb 11, 2004

Ok here's a big one.

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:

qtQN__________Date
---------------------------
111q0001--------02/01/04
111q0002--------02/02/04
111q0002A------02/03/04
111q0002B -----02/04/04
222q0005--------01/15/04
etc... ------------- etc...

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

View 6 Replies View Related

SQL Server 2012 :: Parse A String Every 6 Letters

Jan 28, 2015

How to parse a string to equal length substrings in SQL

I am getting a long concatenated string from a query (CTVALUE1) and have to use the string in where clause by parsing every 6 characters..

CREATE TABLE [dbo].[PTEMP](
[ID] [char](10) NULL,
[name] [char](10) NULL,
[CTVALUE1] [char](80) NULL
)

INSERT INTO PTEMP
VALUES('11','ABC','0000010T00010L0001000T010C0001')
select * from ptemp

After parsing I have to use these values in a where clause like this

IN('000001','0T0001','0L0001','000T01','0C0001')

Now ,the values can change I mean the string may give 5 values(6 character) today and 10 tomorrow.. So the parsing should be dynamic.

View 2 Replies View Related