Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Removing White Spaces In A Varchar Column


I have a table . It has a nullable column called AccountNumber, which
is of varchar type. The AccountNumber is alpha-numeric. I want to take
data from this table and process it for my application. Before doing
that I would like to filter out duplicate AccountNumbers. I get most of
the duplicates filtered out by using this query:

select * from customers
where AccountNumber NOT IN (select AccountNumber from customers where
AccountNumber <> '' group by AccountNumber having count(AccountNumber)[color=blue]
> 1)[/color]

But there are few duplicate entries where the actual AccountNumber is
same, but there is a trailing space in first one, and hence this
duplicate records are not getting filtered out. e.g
"abc123<white-space>" and "abc123" are considered two different entries
by above query.

I ran a query like :

update customers set AccountNumber = LTRIM(RTRIM(AccountNumber)

But even after this query, the trailing space remains, and I am not
able to filter out those entries.

Am I missing anything here? Can somebody help me in making sure I
filter out all duplicate entries ?

Thanks,
Rad


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Removing Spaces From An Nvarchar Column
Hello,This is a simple question, hopefully with a simple answer. I havean nvarchar column of length 255. In one of the rows I have thefollowing sentance - 'See the brown ball bounce'. Is it possible touse a command to remove all of the spaces in that sentance, so thatthe sentance reads 'Seethebrownballbounce'? As you can see, I am notjust interested in getting rid of the trailing and leading spaces.Thanks,Billy

View Replies !   View Related
White Spaces
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 Replies !   View Related
White Spaces In Data Fields
Hi,
Whenever I insert a record into my table it adds trailing white spaces up to the amount of char's that the field is set to allow.  Obviously I don't want it to do this.
Among other problems then when I get the data back out it has a ton of white spaces, which normally wouldn't be a problem.  i could just use the .Trim() function, but for some reason when I bind the data to a drop down list and use the .Trim() function it doesn't trim the white spaces.
Anyways any ideas on how to make it so the white spaces don't get put in in the first place??  Or any other thoughts on this??  Thanks!

View Replies !   View Related
Spool File (white Spaces)
My problem seems to be very simple to solve but I don't know how to do this ;-(
I'm trying to make a query spool (by osql command line) to a txt file and white spaces are also spooled and I don't know how to avoid it.

osql -E -u -h-1 -s "," -i test.sql -o test.txt

test.sql:
GO
SET NOCOUNT ON
select /.../
GO

test.txt:
1, 3, 2129, 0
2, 9, 2345, 9
/.../

and I'd like to have the output file like that - without leading spaces:
1,3,2129,0
2,9,2345,9
/.../

Thanks in advance for your help.

View Replies !   View Related
How To Remove White Spaces Between Words
What is the Select statement to remove white spaces between words?

View Replies !   View Related
Trim Trailing White Spaces
Hi All,

I have a column which has some white spaces that I suspect is tab delimeted one. So when I use a rtrim(col1) it would not trim those. So i used a scrip component and wrote this line,

Row.trimDetail = RTrim(Row.detail)

here trimdetail is an o/p column and detail is the input col with the trailing spaces.

but still I don know why the column has that spaces. Can someone help me to figure out what is the problem ?

Thanks in advance,

View Replies !   View Related
Values Entered In Db Has Trailing White Spaces
Hi, I'm inserting a few columns into my db (they all have a nvarchar(50) ).. but i noticed when i retrieve them out of the db, the length of the string always have some trailing white spaces behind them and such when I try to do stuff like    dropdownlist.items.findbyvalue(),  it normally fails.I did trace and before the string get into the db, they were teh right length. so I'm not sure where did I do things wrong? thanks

View Replies !   View Related
Preserve Leading And Trailing White Spaces On Report
I spent huge amount of time figuring out how to preserve lading and trailing white spaces on report display without success. Can anyone help me here?

My problem is I have data with leading and or trailing white spaces and I need to show it as is. In designer preview it shows correct values. As soon as report is published and accessed on web, it truncates the whitespaces . I had a look at source, it shows values are correctly fetched(with spaces) but are ignored while rendering. I also tried replacing blank space with &nbsp;, however it reads this as &amp;nbsp;.

I am using asp.net 2.0 and SQL serer 2005 reporting services.

View Replies !   View Related
Create TRIGGER Remove White Spaces From A Fields In Table-scan And Fix
hi
i have table i use it for update insert
and the users use this table from a grid on the web
and i need to prevent from white spacein thefields in table
so how to
create TRIGGER remove white space from a fields in table scan and fix it?





Code Snippet
SELECTTRIM(fieldname)
,LTRIM(fieldname)
,RTRIM(fieldname)
,LTRIM(RTRIM(fieldname))
FROM tablename





Code Snippet
WHERE (LTRIM(RTRIM(fieldname)) = 'Approve')




Code Snippet
replace(@text,' ','')







create TRIGGERon update insertand not to damage the text in the all fields
TNX

View Replies !   View Related
Removing White Space From Collapsed Row Groups
Hello

I have a matrix. There are 2 row groups. The visibility of the second group is toggled by the first group. The visibility of the corresponding text box is also toggled similarly. So when the report runs, initially only the first row group is visible, and clicking on the plus sign makes the column for the second row group appear.

This is great, except that I can't get the column of the second group to disappear completely when the group is invisible. This means that there is a gap between the first column and the actual data in the detail boxes. I want this data to shift along from left to right as the row groups are expanded.

Does anyone know if this is possible? Any help would be much appreciated.

Thanks
Dominic

View Replies !   View Related
Removing Spaces From A Text
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 Replies !   View Related
Removing Words And Spaces Using DTS
I have a table of addresses I need to import each month and under the 'County' Column the word County or Burrough follows each county name. Ex 'Baltimore County', 'Washington County', 'Davis Burrough' etc.

Using DTS is there a way to remove the space and County(Burrough) following each county name? I've tried using the trim feature but that only seems the Trim the White Space.

Thanks for any help you can offer.

View Replies !   View Related
Reportviewer Removing Spaces
Hi there,

I have a text box with the following expression:

="OEM Part Code" + " " + "Part Code" +" " + "Part Description"

As you can see, there is a lot of spaces in here. The reportviewer removes these spaces. so that it look like
OEM Part Code Part Code Part Description"

Instead of
OEM Part Code Part Code Part Description

Why is it doing this?
Can I stop it from doing this?

Regards
Mikey

View Replies !   View Related
Removing Spaces Between Words In Sql
I guess there is no built in functions to do this but I have a function that replaces anything that is not A-Z with a space andreturns @data. What I additionally need the function to do is scrunch up @data (remove all blanks betwwen each word so that 'I ran very fast' would be 'Iranveryfast').

What I need help in doing is the "Scrunch" part. Is there a way I couldmove the @Data to something like @DataHold and inspect each character, if it is not a blank, move that character back to @Data?
This was pretty easy for me to do in C# with a while loop, but I do not know how to get it done in SQL Server 2005.

Thanks for any help!



View Replies !   View Related
UPDATE - Removing Trailing Spaces
I have three columns, RecordID, FirstName, and LastName, but somehowthrough some program glitch, there is sometimes a trailing space inthe firstname and lastname columns, for example, a persons name couldbe entered as "John " "Smith" or "Bob " "Johnson "I know there is a RTRIM function in sql, but the problem I/m having ismaking an update line go through each row, and removing trailingspaces on those two columns. Any help will be greatly appreciated.Thanks in advance.

View Replies !   View Related
Removing Leading And Trainling Spaces
I loaded data into a CHAR fields from Legacy.
How to remove those leading spaces with in SQL server...?

View Replies !   View Related
Removing Records With Spaces In Fields
How do i eliminate records which has spaces

i tried with

select * from table
where col1 !='' or
col1 is not null

which doesn't work.. any help.

View Replies !   View Related
Removing Non-alpha Characters && Spaces Script...
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 Replies !   View Related
Removing Empty Spaces Between Main Report And Subreport
Hi All,

I have a main report that does some row groupings.. When there is little data coming out, there tends to be a widish gap between main report and sub report..

I tried placing the sub-report in the table footer but this landed up squashing the column width's of the sub report..

Is there another way to get this right ? I tried placing the sub report into a rectangle, and the main table into its own rectangle and then merging those into another rectangle.. This unfortunately did not work..

Any Suggestions would be appreciated

Kind Regards,
Neil Stuppel

View Replies !   View Related
Removing The White Space In Between The Image And Page Border Of The Page Header
Hi Team,


When i view the Report from SSRS Report preview Tab it's working fine, But when i deploy that and try to view in the IE
I am seeing the Body background color in between the image and page border of the page footer how to solve that?


View Replies !   View Related
Varchar And Blank Spaces In SQL DB Field
I am trying to load a field in my DB and it is defined as varchar(11) but when I populate it, it still adds spaces at the end. When I try to use it in an If statement, it doesn't match and executes the else instead. The wierd part is it seems to make it 10 characters long and not 11 or the the actual length. I think I had originally set it up for char(10) then changed it afterward but I even deleted the field and reentered it as varchar(11).Thanks,Eric

View Replies !   View Related
Varchar Truncating Trailing Spaces
I have tried setting SET ANSI_PADDING ON and SET ANSI_PADDING OFF
neither works.

I am trying to do this (in a loop though)
Declare @vc50_execstatement varchar(50)
Select @vc50_execstatement = 'grant select on '+name+' to USER01' from sysobjects where id = [whatever]

What I end up with is
Grant select ontablename to USER01

Note there is no space between on and the tablename.
Must I change all dynamic sql using varchars to eliminate this problem?

I have a later build than Beta 3 (RC1)

Jim Craddock

View Replies !   View Related
HTML 4.0 Getting White Column On Body
Hello,

I have started a report that looks great exported to PDF, excel, looks great on preview in visual studio, but has a strange issue when accessing with URL access using the format=HTML4.0 option. I tried it both with and without text (thinking removing text would give clues, no joy.)

Here's a screenshot of it: http://img451.imageshack.us/img451/4505/iffyreportyq8.jpg

When printed out using print buttonit looks fine, but on the preview (as in screenshot) it has narrowed the body and got an approx. 1 inch wide white column on the right between the header and footer.


I've tried searching the web for clues on how to fix this with no success. Any help would be much appreciated.

Greg

View Replies !   View Related
Problems Moving Data Over 8000k In DB2 Varchar Column Into SQL Server Varchar(max) Using SSIS


I have looked far and wide and have not found anything that works to allow me to resolve this issue.

I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.

I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".

[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".

[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?



View Replies !   View Related
Any Easy Way To Change Column Length Like Varchar 30 To Varchar 100?
I would like to increase the length of the column fromLOCNumber VARCHAR(30) DEFAULT ''toLOCNumber VARCHAR(100) DEFAULT ''without losing any data currently stored in the field.And it has an index on it defined asCREATE INDEX LOCIndex ON BookData(LOCNumber)Thanks for any help.

View Replies !   View Related
Getting Spaces In A Column
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 Replies !   View Related
All Spaces In A CHAR(5) Column
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 Replies !   View Related
Inserting A Value With Spaces Into A Column
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 Replies !   View Related
Spaces In Column Names
Hi all,

Is it a bad practice to create column names with spaces. like [Last Modified On]?

If yes, y?

View Replies !   View Related
In Print Preview Mode White Is Black And Black Is White...
When I go to preview mode and select the print preview. The white background is black and the black is white. Any one know how to set this back to the original way it worked?



View Replies !   View Related
Need Your Help To Remove Spaces In The Column Entries Using SQL
Hi all,I am new to these so plz never mind if this is funny.here is my problem :Table : moodyColumn : TitleNew column : NospaceI have data in "Title" column of many rows which are normal sentence.My requirment is to remove the "white space", +, | , ., / , ! @, $, %etc special characters and fill it by ( hyphen) and put it in new"Nospace" ColumnExample :I have : Hurray ! I won the GameNeeded : Hurray-I-won-the-GameCan any body helpme in getting an SQL Query for this if possibleThanks in Advance

View Replies !   View Related
Fill Up Spaces With Dots In A Column
Hi,

I currently have a column in a table with data type char and length 500. However, not every column fills up the entire 500 length, and I would like to fill up the rest of the spaces with dots. Is there a setting in SQL to do this? I do not want to use varchar since I want a fixed length with dots at the end. Any ideas?

Thanks,
Alan

View Replies !   View Related
BCP XML Format File Fails If Column Name Contains Spaces - Looks Like A Bug


Hi,

I am using the following command
master..xp_cmdshell 'bcp Staging.dbo.TableA format nul -f "\CommonTableA.xml" -x -r"" -c -t -T'

to generate a XML format file so thatI could later use during import process and the structure of the table is
CREATE TABLE TableA
(
[First Name] AS VARCHAR(50),
LastName as VARCHAR(50)
)

it fails, it throws Error = [Microsoft][SQL Native Client]Format File : Invalid Field index.
When I change the column name by removing space it works.
Is there a work around for it, the reason being the Tables and format files has to be generated on the fly as its a requirement and I want to use the XML format instead of FMT type.

Thanks
Sathish

View Replies !   View Related
DTS- Destination Column Name Has Spaces Causing Error
I am using Advantage ODBC 6.2 to connect to a Advantage Data Server to
push data from a SQL table into this server.  I can view the data from
the ADS with DTS and I can insert data by using "Insert into TempTable ("Last
Name","First Name") values ('test','test)" from a Execute SQL Statement. The issue is when I build
up a DTS package to pull from SQL into the Advantage ODBC, I get a
"missing closing ")"" error.  I have narrowed it down to the column
names in the destination table having spaces (Last Name, First Name,
and a bunch of others.  DBA 101 here).  How do I beat this?


I have tried editing the destination column names in disconnected edit
by adding the double quotes but get a "Column Name '"Last Name"' not
found" error.  Wrapping them with [] doesn't work.  I think this is a
limit on Advantage.


When I try to use Advantage OLE DB, I get an 'Ace32.dll must be newer
then the other dll" error.  I am afraid of upgrading Ace32.dll and not
break other things.

 

So with this said my two questions:

How do I get the destniation column names wrapped in double quotes when using transformations?

or

Is there a way I can do an Insert into Advantage connection(column names) values (Select Values from SQL Server connection)?

View Replies !   View Related
Ragged Right Export Trimming Off Leading Spaces In Last Column
I have to send a field delimited file which is exported from our SQL server to another agency.  The format is very strict.  It has to be 57 chars for the total record with CR/LF at the end of every column.  There can be no exceptions. 

In the past, I did a normal fixed width export from SQL Server 2000 and chose the end of line markers as CR/LF.

We have newly upgraded to SQL Server 2005 and everything is workign great except my simple DTS file export.  With the DTS changes, a normal fixed width export will not put the CR/LF at the end of the rows which means that they reject our file.  That means I have to do a ragged right export.  When I do this, it trims the trailing spaces out of my last column before adding the CR/LF to the end or each row.  If it is supposed to be fixed width, why is this trimming my trailing spaces off of there?  You would think that it would leave my last column as a width of 5 regardless of spaces.   Those trailing spaces must be there for the file to work properly.

This makes absolutely no sense to me.  Perhaps someone can shed some light on this or explain me what I am doing wrong.

View Replies !   View Related
Removing Duplicate Value From One Column.
Hi all,
I have one table in which one column contains duplicate values. My question is how i can use T-SQL so that i can retrive values for all columns in the table which are distinct and retriving the single value from column which contains duplicate values.

(I know distinct)
Thanks in advance.

Minesh.

View Replies !   View Related
Removing Characters From Column
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 Replies !   View Related
Removing Identity Column
How to remove identity property from a column throught SQL statement?

View Replies !   View Related
Removing Characters From A Column
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 Replies !   View Related
Removing Text From Column Name
Hi all
i want to remove text from my column name using query.
for example
i have the product name like "silver 8' trampoline pack "
i need to remove "silver 8' " and want to display only trampoline pack
similarly if I have product name like "gold 8' trampoline pack"
i need to display only trampoline pack.
can anybody help me in this regard?

thanks

View Replies !   View Related
Removing Identity Column
We can easily remove identity columnn through enterprise manager but how can it be done through transact sql?

The only way i found is to create a new column and pass values if identity column in it and then remove this identity column is there any better method of doing it?

View Replies !   View Related
Removing Numbers From A Column


Hi,

I have the following tables :




Code Block
Create table #EmployeeList(empname nvarchar(20), emptype char(5))

Insert INTO #EmployeeList('Cary zzz',null);
Insert INTO #EmployeeList('01Jack',null);
Insert INTO #EmployeeList('02 Tommy',null);
Insert INTO #EmployeeList('03 Ricardo',null);
Insert INTO #EmployeeList('04 Jack',null);
Insert INTO #EmployeeList('Les zzz',null);
Insert INTO #EmployeeList('05 Tim',null);

The final data looks like this :

Cary zzz NULL
01 Jack NULL
02 Tommy NULL
03 Ricardo NULL
04 Jack NULL
Les zzz NULL
05 TimNULL






1. I want to delete all rows which have 'zzz' in it.
2. I want to remove the numbers from the empname column




Code Block
Expected Output :

Jack NULL
Tommy NULL
Ricardo NULL
Jack NULL
TimNULL






Can anyone help me please with the query?

thanks.


View Replies !   View Related
Removing Partial String From Column
how do i update a table which has like two strings in 1 column like

blog, joe ?

i want to strip the joe into a new field and the blog into another field

update Agency
set firstname= substring(firstname,charindex(' ',firstname)+1 ,len(firstname))

i managed to strip the first name which is the string at the back but not the last name which is the string at the front

View Replies !   View Related
Removing Characters From Column Data
How can I remove characters that appear to have the effect of carriage returns or tabs, and they appear at the end of the data in a column. They look like small boxes lined up along side each other. I cannot find a way to clear these out of a particular column and when I move data from the table to a file it creates blank rows where ever these boxes appear.

Any help would be appreciated.
Thanks

View Replies !   View Related
Removing Non-numeric Digits From Column
Using the following:

select hl, substring(hl,1,patindex('%/%',hl)) as test
from appointment

returns

hl test
A PCM/RODRIGUEZ A PCM/
Y OPTOMETRY/VISUAL FIELD TESTSY OPTOMETRY/
W DENTAL/DUNDON W DENTAL/
Y LAB
Y PCM/NEMES F/U Y PCM/
W NUTRITION/FIRM-A (ROOM E116)W NUTRITION/
W FIRM-A/SILVER/ABBOUD IIW FIRM-A/

I want to be able to remove the first 2 digits and the / to just have the clinic only remaining. Note that Y LAB is not listed in the test column..why? Any help is greatly apprecitated. Thank you....

View Replies !   View Related
Removing Identity Property Of Column
How to remove an Identity property for an Identity Column

View Replies !   View Related
Removing All Hypertext Links From A Column
Is this possible to do using regular expressions in sql 2005?

View Replies !   View Related
Removing Commas From Column - Now It's A Mile Long
Hello,

I recently used the REPLACE command, as described in a previous topic on this forum, to remove unwanted commas however I've now got a new problem, the column has become half a mile long. I was asked to raise a new topic and give examples, see below:

CAN ANYONE TELL ME:
1. Why is the column now bigger?
2. How can I redue the size of the column to it's origional size?

I have already attempted to use CONVERT, RTRIM and CAST around the replace command, all give an error.


Example query and result before REPLACE:
select ICMAFinInstName,CptyCode from tradedetails

ICMAFinInstName CptyCode
---------------------------------------------------------------------- ----------------

Example query and result using REPLACE:

select replace (ICMAFinInstName,',',' ')AS NoCommaInst,CptyCode from tradedetails

NoCommaInstrument CptyCode
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------

(I see in the preview that this forum has removed the spaces between the titles, but the dashes (--) show the size of the columns

Thanks

JM

View Replies !   View Related
Com Exception When Removing ErrorOutPut Column From The Collection.


OutCollection[1] holds the erroroutput columncollection.

metaData.OutputCollection[1].OutputColumnCollection.RemoveObjectByID(errOutputCol.ID);

some times 1 Or 2 columns gets deleted, after that the exception is raised.

View Replies !   View Related
Hiding Or Removing Column Output From Select Statement
I'm executing the following...

select COL1, min(COL2) from TABLE group by COL1

the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed.

I was hoping a simple
"delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)"

would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect?

this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so.

tia

a

View Replies !   View Related
Removing A Column Enabled For Full-text Search
I'm trying to create a script to clean up some columns between two revisions, and I'm having trouble with one that is full-text enabled:

ALTER TABLE tblNote DROP COLUMN colComments

I get the message:

Cannot alter or drop column 'colComments' because it is enabled for Full-Text Search.

I can't seem to find a way to remove the full-text index using scripting.

Any suggestions would be appreciated.

Geoff.

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved