Removing Certain Characters

Aug 18, 2006

Hello all,

I have a table named users. It consists of user names, user ids, etc... The problem is that whoever designed the ASP code before me allowed people to enter info in any format they want. This poses a problem because now the name can have 1, 2, or sometimes 3 spaces in between the last and first names. And sometimes the middle initial is used with a period following it. This creates problems when I am trying to execute a Select statement since it won't match if there are an unknown number of spaces in the string and throws an error when a period is used.

Is there a SQL query I can execute to change all the user_names into a format such as the following:
LastName, FirstName MiddleInitial

Like I said, it is already almost the same, just has too many spaces and some have periods after the middle initial

View 5 Replies

Removing Characters From Column

Apr 19, 2001

I have a table called exchange and field called address. The rows(1400+) in the field look like:
MS:VA/Celcmv/VHACLEADAM%SMTP:Doe.Jane@med.va.gov%X200:c=US;a= ;p=av;o=Celcmv;s=Doe;g=Jane;

How do I remove everything to the left of doe.jane@med.va.gov and everything to the right of doe.jane@med.va.gov using query analyzer? Thank you in advance...

View 3 Replies View Related

Removing Characters From A Column

Sep 19, 2001

Can someone please suggest a function to remove the last 3 characters from a column? I was thinking of the LEN function, but I am unsure of the syntax.

Thanks!
Lisa

View 5 Replies View Related

Removing ASCII Characters.

Feb 16, 2001

I've got one SQL Server 7.0 table with a "Decsciption" Column of length 4000. The values in this column contains "End of Line" ASCII Character. The ASCII Value of this character is 10. I'm not able to remove this ASCII Character. I tried by using REPLACE function. But i could not remove that character.

Any thoughts are welcome,

Regards,
Santha.

View 1 Replies View Related

Removing Non-printing Characters ...

Jul 20, 2005

Folks ... I have a pipe-delimited ASCII text file with a lot ofdifferent non-printing characters. Rather than try and figure out allthe non-printing characters that exist in this 17+ million recorddatabase, I was hoping someone might have already written a scriptthey'd be willing to share that would remove all non-printingcharacters from an ASCII file?Thanks,Ralph NobleJoin Bytes!

View 1 Replies View Related

Removing Invalid Characters

May 6, 2008

here is my data
112344*100
23*34444

I want to remove * and make sure the length is 11 digits and and add leading 0's

Desired data
00112344100
00002334444

How will i be able to achieve this..

View 2 Replies View Related

Removing Characters In Field Grouping

Jun 15, 2007

Hello,

I am using the following expression to strip the last 11 characters in a field group and it returns an error saying that Len cannot use a negative number. It must be 0 or greater. Is there a better alternative?

Code Snippet

left(Fields!TestName.Value,len(Fields!TestName.Value)-11))

View 2 Replies View Related

Removing Special Characters From A Number String.

Jul 11, 2001

Hi,

I was wondering what would be the best way to remove special characters like, '-', '&' '(',')','#','*', etc... from a number string. To be specific a phone Number string where the string is >= 10.

Thanks, Mark

View 1 Replies View Related

Removing Special Characters From Existing Data

Jun 1, 2006

I have a table with several columns of information that I wish to set up some form of schedule to go through this data and remove any special characters that may interfere with other code processes.

Mainly the coma's and the apostrophes. It really messes with my asp pages and scripts when retrieving this information and trying to do other things with it, so I need to figure out how to remove these from the tables so it does not cause these issues.

Knowing this, I cannot figure out how to keep the data in the row/column and just extract the special characters from that data. The other problem is, everything I try requires me to insert either a coma or apostrophe as part of the code string which in lies my issue.

How can I parse through my data, leave the data as-is, but just get rid of coma's, apostrophes, and double quotes?

Does anyone have a basic example that I can use to expand on?

View 1 Replies View Related

Removing Non-alpha Characters && Spaces Script...

Dec 6, 2006

Hi I am trying to strip out any non-alpha characters from a field.

i.e. Field = ABC"_IT8*$ should return: ABCIT8

I am writing a loop to do this for all values of a field. The script runs, but hangs....please could somebody advise on the code below...:

I run the script but it doesn't seem to finish. Can anybody see any issues with the code:

DECLARE @Index SMALLINT,
@MATCH_Supplier_name varchar(500),
@Counter numeric,
@Max numeric
-- @sqlstring varchar(500)

SET @Counter = 1
SET @Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

WHILE @Counter <@Max
BEGIN
SET @MATCH_Supplier_name = (SELECT Match_Supplier_Name FROM SUPPLIER_TABLE_TEST WHERE @Counter = DTect_Supplier_SRN)
SET @Index = LEN(@MATCH_Supplier_name)
WHILE @Index > = 1
SET @MATCH_Supplier_name = CASE
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[a-zA-Z]' TH EN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
WHEN SUBSTRING(@MATCH_Supplier_name, @Index, 1) LIKE '[0-9]' THEN SUBSTRING(@MATCH_Supplier_name, @Index, 1)
ELSE ''
END + @MATCH_Supplier_name
SET @Index = @Index - 1
--PRINT @MATCH_Supplier_name
SET @Counter = @Counter + 1
END

View 2 Replies View Related

Removing Junk Characters From One Column Of Table

Mar 12, 2012

I have a table and one of the column have junk characters in it, how can I remove the junk characters?.

Eg : Employee
Eid Ename
1 a�
2 �ddd

how can i remove Junk characters and get only Enames from above table.

View 4 Replies View Related

SQL 2012 :: Removing Consecutive Characters From Middle Of A String

Apr 14, 2015

I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?

Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.

I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.

View 9 Replies View Related

SQL Server 2012 :: Removing Greek Characters From A String

Sep 17, 2015

I have a varchar field which contains some Greek characters (α, β, γ, etc...) among the regular Latin characters. I need to replace these characters with a word (alpha, beta, gamma etc...). When I try to do this, I find that it is also replacing some of the Latin characters.

DECLARE @Letters TABLE (Letter NVARCHAR(10))
INSERT INTO @Letters VALUES ('a'), ('A'), ('b'), ('B'), ('α')
SELECTLetter, REPLACE(Letter,'α','alpha')
FROM@Letters

In this case, the "α" is being replaced, but so are "a" and "A".

I have tried changing the datatype from varchar to nvarchar and also changing the collation.

View 4 Replies View Related

Removing Non-numeric Characters From Alpha-numeric String

Oct 24, 2007

Hi,

I have one column in which i have Alpha-numeric data like

COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.

I want to remove Non-numeric characters from above (space,'(',')',+,........)

i want to write something generic (suppose some function to which i pass the column)

thanks in advance,

Mandip

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

Removing *

Aug 18, 2005

Hi! I am trying to remove an "*" I used to transfer a file from Excel to SQL. I put the "*" in the NULL values so that I would not get an error. Now that I have the file in SQL, I need to remove the :*" from some columns in my table. How can I select all of those in a column and delete them without deleting each individual one. Any ideas?

Thanks for your help.

Scott

View 6 Replies View Related

Removing Old Bak

Jan 15, 2008

Started with maintenance cleanup task set up bak and directory did not delete the files.

Then found this http://blog.solidsoft.com/blogs/richards_infrastructure_blog/archive/2007/07/12/369.aspx

Copy script

use master
declare @dt datetime
select @dt=getdate()-2
EXECUTE master.dbo.xp_delete_file 0,N'D:BackupAcctDB',N'BAK',@dt

Get message

Msg 22049, Level 16, State 1, Line 0
xp_delete_file() returned error 2, 'The system cannot find the file specified.'

Anyone else get problem with removing BAK files

View 4 Replies View Related

Removing Padding From Var

Feb 7, 2008

I have a search box to look up using a number as an identifier. Currently my query is where x = @enteredNumber, but some place where a user might get the number it is padded with zeros. We can have them just see the number and retype it.
EG: 000000123 would be entered as 123.
But, I would like to let them copy and paste, but then strip the zeros (left padding only).
EG:
@num = stripPadding(@enteredNumber)
IE:
@num = stripPadding(000000123)
@num = 123

View 2 Replies View Related

Removing A Default...

Sep 4, 2002

Hi,

I'm using SQL Server 7.0 SP3. I added a column to a table like so:

ALTER TABLE T704_RELATIONSHIP_INDEX_CLIENT_REPORT_STORE
ADD Universe_Member_Set_cd INT NOT NULL DEFAULT 1

What is the syntax for dropping the 'DEFAULT' from this column once it has been created? I've looked in BOL but can't seem to get the syntax correct.

Thanks in advance,
Darrin

View 1 Replies View Related

Removing The Duplicates

Aug 2, 2000

I need to remove the duplicates from a table. If a record exits 4 times in a
table I need to delete 3 records and retain only one occurance of that.

I need a query to do this.

Can anybody help.

-Rajesh

View 3 Replies View Related

Removing BuiltinAdministrators

Mar 20, 2002

Hi all,

I am using SQL 2k SP2 on Win 2K Advance server Cluster. My problem is how to remove builtinAdministrators login. Earlier when I tried it was doing failovers from one node to another and not stopping. I was not able to do anything. Any help is appriciated

thanks,
Minesh.

View 3 Replies View Related

Removing DB Suspect

Aug 31, 2000

I wonder if one can give a hint on remove a DB suspect,
Mind you I have used SP_dbremove still did not remove the DB
This is on SQL 7.0 box

Any idea welcomed

Saad

View 2 Replies View Related

Removing A Filegroup

Apr 25, 2000

Hello,

A few days ago one our database's default filegroup filled up. To solve the problem one of my colleagues created a new filegroup. This didn't solve the problem as no objects were placed on this filegroup and the initial one is still full. The idea was to expand the original filegroup over a new physical disk, not to create a new filegroup on that disk. Unfortunately, this is what happened.
To solve this I want to delete the new filegroup, but when I issue the command 'alter database PvgOmcsOds remove filegroup ternary' I get the message 'Server: Msg 5042, Level 16, State 7, Line 1 The filegroup 'ternary' cannot be removed because it is not empty.' Yet there're no objects placed on this filegroup.
How can I get rid of this filegroup?

Stef

View 2 Replies View Related

Removing Transaction Log From SQL 7 DB

Apr 7, 1999

I am trying to remove a transaction log that has grown too large for its databse and server. I have truncated the log and have run dbcc shrinkfile with the EmptyFile Option, but when I try to use alter database remove file and am I told that the object is not empty and cannot be removed. The server is in dbo use only.

What am I missing?

View 1 Replies View Related

Removing Filegroup

Mar 8, 2001

Hi all,

I am trying to remove one of the filegroups in the development database to shrink its size down. I started with emptying and removing its files and ran:
dbcc shrinkfile
(pubs_data_1,emptyfile)
go

and it ran fine, with 'DBCC execution completed' message

but when I tried :

alter database pubs
remove file pubs_data_1
go

I got 'pubs_data_1 cannot be removed because it is not empty'.
I even tried to go to each of the tables and delete from them first which didn't work, then drop all tables in that filegroup (I knew which tables are located on that filegroup), that didn't work either.

Any ideas? Is there a way to check what else sits on that filegroup?

Thanks

View 2 Replies View Related

Removing Replication

May 8, 2003

Hi,

We had transactional replication set up by a consultant with remote distributer, push subscription to the publisher (main production database). However, the subscriber machine had fatal crash and is unrecoverable. Now, replication jobs have been failing on distributor and we immediately need to disable and remove replication as it has started causing problems with transaction log backups on production database.

How can I completely remove this whole replication from publisher and distributor?

Please help.

Thanks,
Shaili

View 5 Replies View Related

Removing SQL 6.5 Software

May 28, 1999

Dear Concerned DBAs,

I am wanting to pick someones brain and find out if there is anything I can do to successfully remove and reinstall SQL Server Version 6.5? I have already removed the software from the SQL Setup icon in ProgramsMSSQL Server 6.5 and went and removed the setup.exe and mssql files associated with the software from the server. Now when I attempt to reinstall the software back onto the server after a hard reboot, I do not get the option once the SQL Server Version 6.5 disk is placed into the CD-Rom to install SQL Server. The only options I get are upgrade, server configurations, etc.

Can anyone or everyone give me a hand as to where to take this dilemma other than rebuilding the server from the ground up?

Anticipatorily Yours,
Daimon Russell

View 3 Replies View Related

Removing Duplicates

Jan 27, 2005

I have a members table and have added an extra few thoushand members to it. Now I need to remove the duplicates.

It doesnt matter which duplicate i remove as long as there are unique email addresses.

so here is the format of the table:

id
email
firstname
lastname
datebirth

if i do a:

SELECT COUNT(DISTINCT Email) AS Expr1
FROM Customer

it returns 21345

and

SELECT Count(Email)
FROM Customer

returns 28987

I can get the unique email addresses into another table by going:

SELECT DISTINCT emailaddress INTO DistinctCustomer
FROM Customer

but this will only return unique email addresses. How do i select distinct email address and all other fields into a new table? or just remove duplicates where email address appears more then once?

Thanks in advance

Tom the SQL beginner

View 5 Replies View Related

Removing A Paratiotion

Jun 24, 2008

i want to build a paratiotin which the key of the paratition will be a datre in this structure :
20080101235959
20080102235959
20080103235959
20080104235959
20080105235959
and so on.....
and i want that when there is a paration that it's "date" is more then 30 days to delete it (and that its data will be deleted too)
how do i do that?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

View 5 Replies View Related

Removing Primary Key

Nov 27, 2005

Hi How do i remove the primary key for this table

create table table2
(col1 int,
col2 int,
primary key(col2))

Thanks
Vic

View 2 Replies View Related