Convert Punctuation To Spaces?

Jul 20, 2005

Hi,

I have a table of text. I need to search for whole words within this text...
For example, I need to be able to search for records that contain 'dog' but
not return 'hotdog' or 'dogma' for example.

I am doing this by throwing a space around both the records in the table and
the search word like this:
WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')

The problem is that punctuation needs to be stripped out of the text so that
it will still find "...walking the dog."

Is there a way to update, converting a certain set of characters into
another character (i.e. a space) and/or to do the same thing during the word
search query itself?

Thanks!

View 5 Replies

Convert Decimal To Char And Add Spaces?

Jan 2, 2002

Using SQL 2000 I have data in a sql table that is store in varchar like below

5.00
15.00
9.00

The integer part will never be bigger than 20. I need to move it to another SQL table that is char(5). I need the results that go in that table to like like below

05.00
15.00
09.00
I looked at the replace and cast but couldn't get the results. Any better approaches?
Thanks

View 3 Replies View Related

Punctuation ?

Mar 12, 2004

Hi
I have this simple and working well SQL-row:

MyCommand = New SqlDataAdapter("SELECT * From Tbl_Table where Type = '" & TestType.tostring & "' ", MyConnection)

-----------
Then I have this one which I am very proud of because I can retrieve any word or part of word from the Item-field:

Test_Text=(Session("session-Text").tostring)

Dim val1, val2, val3 as string
val1 = Test_Text
val2 = "%"
val3= val1 + val2

MyCommand = New SqlDataAdapter("SELECT * From Tbl_Table where Item Like '%" & val1 & " %' OR Item Like '%" & val3 & " %' ", MyConnection)

------------
The problem is:
When I try to merge these two in one big "AND" row as shown below, It only cares of picking up the Test_Text string and ignores totaly the TestType one!
I guess it is a matter of punctuation, can any one help me? Thanks.

MyCommand = New SqlDataAdapter("SELECT * From Tbl_Table where Type = '" & TestType.tostring & "' AND Item Like '%" & val1 & " %' OR Item Like '%" & val3 & " %'", MyConnection)

View 3 Replies View Related

Stripping Punctuation

Oct 24, 2005

Has anyone had to deal with removing punctuation (commas, apostrophes,etc.) from a column? What is the most efficient way to have thesecharacters automatically removed from the column?

View 3 Replies View Related

Reserved Symbols / Punctuation Marks In Passwords

Dec 9, 2003

Please can anyone help me - I am trying to establish whether there are any reserved symbols in SQL passwords

eg % � " $ ( / < * etc

The SQL BOL has a list of reserved words but I cannot find a list of reserved symbols.

We have a problem where by for example if the main part of the password is "test" and my user wants to add a punctuation mark into this we get "You are attempting to use an invalid character message"

Specifically

"test$" is accepted "$test" is not accepted
None of "te+st" "+test" "test+" are accepted.

Has anyone else come across this and found a list of the logic that produces these results so I can point my users in the right direction?

Cheers

View 3 Replies View Related

SIMPLE Command To Convert String To Number? Not CAST Or CONVERT.

Aug 15, 2006

Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks

View 4 Replies View Related

SPACES

Sep 24, 2001

Please how can remove spaces on a date field and a text field. THanks.

View 1 Replies View Related

Spaces In A Sql Server

Nov 19, 2007

i collected the users information without using any trim fucntion(i have implemented now)
but the data which has been already posted into my server has text with some white spaces at the beginning of data
now how to remove this white spaces in this column in online server data.
the data is something like this
mycolumn
data
data
data
data

so how to remove white spaces in the above column

View 3 Replies View Related

Add SPACES In Data

Apr 22, 2008

Hi
i have 1 query please guide me,
SELECT P.ProjectName, REPLACE(SPACE(TU.TaskOutlineLevel), ' ', '__') AS dash,TU.TASKISSUMMARY AS TASKSUMMARY,
here i want to add BLANKS but not working so i have to show add spaces using __ here any idea i can add SPACES or ' ' here.
please let me know if any. basically i want to align the data in my GRIDVIEW
Thanks in advance
Parth

View 4 Replies View Related

Remove Spaces

Apr 12, 2006

How can you remove spaces in the middle of a string, RTRIM and LTRIM does not work

View 1 Replies View Related

White Spaces

Jan 8, 2002

Creating a text file using DTS, is there a function/way to take out white
spaces from columns. Example:
'1234 ','567 ' would come in text as
'1234','567'

Thanks in advance.

View 2 Replies View Related

Spaces On An Insert

Jan 7, 2003

Hi,

I am building my insert statements dynamically and am finding that there are spaces after certain integer fields.

example Insert Table A (col1, col2, col3)
values (1 ,'2',3 )

If col1 and col3 are integer fields, is there any affect to either how the value is stored or how it will be retrieved when the table is used in a join. By inspecting the values in the table, it seems fine. Do I need to worry?

Thanks,

Jim

View 1 Replies View Related

Trailing Spaces

Apr 28, 2003

If I run SELECT Len(' ') it returns 0, if SELECT Len('a ') it returns 1
I need this to return the correct length including the space that on the end. I thought it was an ansi_padding problem but even turning padding on results in a 0 length. Any ideas? Thanks!

Todd

View 2 Replies View Related

Erasing Spaces!

Apr 28, 2004

--------------------------------------------------------------------------------

I am tring to join two tables. There is one problem of course. There is one column I would be able to join the two tables by. This column would be Loc_Code. The only problem is that both columns are not exactly the same. They look like this:

Table 1 Table 2
Loc_Code Loc_Code
A 12345 A12345
A 12346 A12346
A 12347 A12347
A 12348 A12348

I need to erase the spaces that exists in the Loc_Code column in table 1 so that I can join with table 2.

All help would be appreciated.

View 9 Replies View Related

Trimming Spaces

Mar 9, 2006

I need to trim what looks like two spaces from a field.

Example:

" 601274" needs to be "601274". Does anyone know the syntax?+

View 9 Replies View Related

Remove Spaces

Jan 25, 2007

is there a way to do remove spaces from a string if its they are in deifferent places on each row

View 1 Replies View Related

How To Ignore Spaces?

Jan 31, 2008

I'm writing a store procedure to accept search strings from user on my site. Currently, this is what I have.

Code Snippet
@schoolID int = NULL,
@scholarship varchar(250) = NULL,
@major varchar(250) = NULL,
@requirement varchar(250) = NULL
--@debug bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * FROM [scholarship]
WHERE ([sectionID] = @schoolID OR @schoolID IS NULL)
AND ([schlrPrefix] LIKE '%' + @scholarship + '%' OR [schlrName] LIKE '%' + @scholarship + '%' OR [schlrSufix] LIKE '%' + @scholarship + '%' OR @scholarship IS NULL )
AND ([Specification] LIKE '%' + @major + '%' OR @major IS NULL )
AND ([reqr1] LIKE '%' + @requirement + '%' OR [reqr2] LIKE '%' + @requirement + '%' OR [reqr3] LIKE '%' + @requirement + '%' OR [reqr4] LIKE '%' + @requirement + '%' OR [reqr5] LIKE '%' + @requirement + '%' OR @requirement IS NULL )

The problem is, somtimes the search doesn't work if there is a space behind or infront of the search string. I wonder if there is away to ignore any spaces and go right into whatever character comes next or after. If so, how do I implement that?

View 7 Replies View Related

Getting Spaces In A Column

Mar 7, 2008

I have a single column value like this. I want to get the charindex value whereever it is a single space.

'6 REP5426 15936 022708 107/0000 33003301985 BAILEY AMY NICOL 11454 PP 25.00 000-00 00110080 A O '

Thanks.

View 5 Replies View Related

Trailing Spaces - Such A Pain...

Dec 31, 2006

Hi All... I'm using a SQL Server 2005 database. I've noticed that columns that are declared as "char" and that have a fixed size tend to put trailing spaces at the end of the data when I pull it out. I guess I can understand why... But it's a pain dealing with it. As I'm bringing my application up, I can see spaces all over the place - I just havent gotten around to doing anything about it yet. What's the easiest/best way to get rid of those spaces. Geez, it'd be real cool if I could put something in the SELECT statement. Any thoughts? Thanks much!! -- Curt

View 1 Replies View Related

Find Leading Spaces

Aug 23, 2001

What is the best way to check for leading spaces in your table, using ltrim?
Such as TableA(name, city)
the data in TableA Smith Dallas
John New York
Greg Richmond
David Chicago
Return only David.

View 1 Replies View Related

Spaces In SQL 7 Database Names

Mar 7, 2000

Hey all,

Hope I can get some help here.

Have a view that has to reference a table in another database on the same server. This isn't the problem. I am just using Databasename..tablename in the from statement. Get the results required in testing.

The problem is that in production the table name has a space character. i.e. 'accounting info' would be the table name. I have not been able to figure out the proper syntax to capture this properly to reference in the from statement.

I have tried enclosing the name in ' ', " ", [], (), {} and just about every combination I can come up with.

Any help in this would be great, and changing the databse name isn't an option at this time.

Thanx,
Chris

View 3 Replies View Related

Replace Null With Spaces

Sep 4, 2003

Hi all smart people,

Can someone please help me with how to replace null values with spaces in select statement.

I have to have a select statement which will select all the data but the field contain null value it has to replace with spaces. How can I do that ?

Thanks,

View 7 Replies View Related

Extra Spaces In Rows

Jul 10, 2006

I have some fields in my database that are char(50)

I use an insert to put some text into the fields (using perl)
when I look at the fields I find that spaces are used to "pad out" the text to the field size.

This didn't happen with MySQL and PHP, is there any way to disable this?

View 5 Replies View Related

Error In Replace With Spaces

Jun 2, 2004

Hi !
I'm trying to put a space after . and , in varchar fields.

I've tried

UPDATE Resultados
SET RespuestaAbierta = REPLACE(RespuestaAbierta, ',', ', ')

but nothing happened

What's wrong ??

Thanks and sorry if my english isn�t so good.

Eduardo (from Argentina)

View 9 Replies View Related

HELP! How To Reduce The Unused Spaces ..

Aug 9, 2004

HI all,

I have a database that allocated:

Data: 7300 MB
Log: 2000 MB

But only used

Data: 5500MB
Log: 50MB

How can I free the unused space in the transaction log because
the database is getting too big.

Thank you for your suggestion.

View 1 Replies View Related

All Spaces In A CHAR(5) Column

Jan 31, 2005

i'm going nuts with this, i suppose i will crack it eventually, but i thought i'd ask around here, seems like all the smart SQL Server guys hang out here

(i'm an SQL guy, not an SQL Server guy)

how does one place 5 spaces into a CHAR(5) column?
create table testzeros
( id smallint not null primary key identity
, myfield char(5)
)
insert into testzeros (myfield) values (' 1')
insert into testzeros (myfield) values (' 11')
insert into testzeros (myfield) values (' 111')
insert into testzeros (myfield) values (' 1111')
insert into testzeros (myfield) values ('11111')
insert into testzeros (myfield) values (' ')

select id
, myfield
, len(myfield) as L
from testzerosno matter what i do, id=6 shows up with L=0, just like an empty string

i've even tried inserting 4 spaces and a non-blank character, which enters just fine, just as you would expect, but when i update the value and replace the non-blank character with a blank, all 5 spaces collapse back to an empty string

is there some kind of server setting like SET ALL_SPACE_EQUALS_EMPTY_YOU_IDIOT to OFF or something?

View 12 Replies View Related

Removing Spaces From A Text

Aug 31, 2006

Hi. In our database, we have a Social Security Number field. We've made application upgrades and we can no longer have the dashes ( - ) between the numbers. So, I ran this update on our database to remove all the dashes. it did remove all the dashes except it put spaces in its spot:

UPDATE DefendantCase SET SSN = REPLACE(SSN, '-','')

so, i tried this query and it does nothing.

UPDATE DefendantCase SET SSN = REPLACE(SSN, ' ','')

does anybody have any ideas? Thanks!

View 5 Replies View Related

Path Spaces Using Xp_cmdshelll

Sep 26, 2006

I know this is probably a DOS question but does anyone know how the following would work without taking out the spaces in the folder "Audit Tax Planning" (works fine in paths without spaces)?

Declare @ren varchar(255)
Declare @path varchar(100)

Set @path = '\mainWGDATAAccountingAudit Tax Planning'--G:AccountingAudit Tax Planning5BE

IF EXISTS(SELECT id FROM tempdb..sysobjects WHERE id = object_ID('tempdb..#Tax_File'))
DROP TABLE #Tax_File

CREATE TABLE [#Tax_File](
[File_Name] varchar(50),
)

Set @ren = 'dir /B ' + @path + 'test.txt'

Insert into #Tax_File ([File_Name])
EXEC master..xp_cmdshell @ren

If not exists(select [File_Name]
from #Tax_File
where right([File_Name],4) = '.txt')
begin
RAISERROR ('File does not exist.', 16, 1)
Return
end

select * from #Tax_File

View 2 Replies View Related

Inserting A Value With Spaces Into A Column

Jun 17, 2008

Hello,

I am fairly new to SQL I have started to administer a system which handles carrier information for a mail order system. The logic behind the system is quite simple there are 5 or so columns in a table the first column is the first part of the postcode i.e EX15, the other columns contain which delivery services and depot numbers are associated with that postcode. It works fine at the moment.

However now the main carrier has decided that they are now going to split these postcodes so for example EX15 1* goes to a different depot than EX15 2*

I cant seem to insert EX15 1 into the first column, I get the following error:

Attempt to store duplicate value in unique column. (-155)

Is this because of the space in 'EX15 1'? Because 'EX15' already exists in that column? In which case do I need to somehow tell SQL that there is a space there?

I hope this makes sense

Below is a snap of the table with the existing EX15 postcode data

postcode|carrier_code|available|depot_code|hub_code|county_code
----------------------------------------------------------------
EX15.....|NF/S93......|........1|34........|C.......|DEVO
EX15.....|NF/SAT......|........1|34........|C.......|DEVO
EX15.....|NF/930......|........1|34........|C.......|DEVO
EX15.....|NF/AM.......|........1|34........|C.......|DEVO
EX15.....|NF/ON.......|........1|34........|C.......|DEVO
EX15.....|NF/48.......|........1|34........|C.......|DEVO
EX15.....|NF/3D.......|........1|34........|C.......|DEVO

Here is a snap of how I would like to set up another entry:

postcode|carrier_code|available|depot_code|hub_code|county_code
----------------------------------------------------------------
EX15 1...|NF/S93......|........1|34........|C.......|DEVO
EX15 1...|NF/SAT......|........1|34........|C.......|DEVO
EX15 1...|NF/930......|........1|34........|C.......|DEVO
EX15 1...|NF/AM.......|........1|34........|C.......|DEVO
EX15 1...|NF/ON.......|........1|34........|C.......|DEVO
EX15 1...|NF/48.......|........1|34........|C.......|DEVO
EX15 1...|NF/3D.......|........1|34........|C.......|DEVO

Thanks

View 3 Replies View Related

HT Make Spaces Render In Asp.net

Jun 22, 2008

All- I'm using the SQL SP below to drive an asp.net gridview. Note how, after "else", I attempt to send to the browser the HTML code that should create a hard space. However, the characters are simply rendered verbatim by the browser. I've tried putting actual spaces in the quotes, and also the SPACE(x) argument. SP test executes appear to show the spaces in the results, but aps.net isn't rendering the spaces at all. Any thoughts.

(Its really more of an asp.net question, but I thought I'd post this here too for your thoughts. Thx in advance.)

SELECT person_id, male, female, last, CASE WHEN fam_adult = 1 then first else ' ' + first end as first, fam_adult, is_adult, is_kid, is_y_parent_or_kid, is_guest, is_person_type_5, grade, school, is_person_type_6, fam_dad
FROM person

View 1 Replies View Related

Trim Spaces In Middle

Jun 20, 2006

In a table I have a coulmn of postal-codes where there is a space in the middle of the postcode. How do I do to trim them away?

View 6 Replies View Related

Replace Blank Spaces

Oct 3, 2006

i have a field with blank spaces.
i wanna replace the spaces with just one spaces. ihave 500 fields in 500 tables.

any input will be appreacited.

i have something like this but its not working.

declare @field varchar(50)
declare @minVoter int
declare @maxVoter int
declare @tableName varchar(20)

set @tablename = '00001'

-- select ad_str1 from [00170]

select @minVoter = min(id_voter),
@maxVoter = max(id_voter) from quotename(@tablename)

while (@minVoter <= @maxVoter)
begin
select @field = ad_str1
from quotename(@tablename)
where id_voter = @MinVoter

update [00170]
set ad_str1 = replace(@field, ' ', ' ')

select @minVoter = min(id_voter)
from quotename(@tablename)
where id_voter > @minvoter

end

View 13 Replies View Related

Issues With Spaces In A Field

Jan 9, 2007

The name field in our database stores the name in the below format:

Shields~Joseph A
Simmons~Russell G
Resig~Benjamin R
Lindsey Jr.~Harold H
Jordan~Adante D
Kerr~Luke D
Adkins~Guillermo B
Conrad~Brian P

I am trying to separate the last name and first name into 2 fields. I do not want the middle initial or Jr. in the field. I am using the statement below but i am running into an issue with names that have Jr. on them because there is an extra space in the name.

SELECT SubString([Name],CHARINDEX('~',[Name])+1, (CHARINDEX(' ',[Name])-1) - (CHARINDEX('~', [Name])-1)) as FirstName,
Left(Name,CHARINDEX('~',Name)-1) as LastName
FROM Employee
WHERE StrtDate >'7/1/06' and StrtDate < '8/31/06' and Status = 'A' and BirthDate < GetDate() - 7665

Any help would be appreciated.

View 1 Replies View Related