rename column

i need t-sql syntax for renaming a column.
the column name contains the [] characters.
alter table <tablename> rename [old field name] to NewFieldName
isn't working for me.
tia,
mcnewsxpthis works:
EXEC sp_rename 'MyTable.[Old Field]', 'NewField', 'COLUMN'|||use sp_rename..
I haven't seen the sytax that you have used.|||You might want to check this out ::
http://vadivel.blogspot.com/2004/08...g-sprename.html
Best Regards
Vadivel
http://vadivel.blogspot.com
"mcnews" wrote:

> i need t-sql syntax for renaming a column.
> the column name contains the [] characters.
> alter table <tablename> rename [old field name] to NewFieldName
> isn't working for me.
> tia,
> mcnewsxp
>sql

Rename A Field Using SQL Statement?

Is there a way that i can rename some fields in a table using SQL from my
.NET program? The customer send sthem in as BMT Number and I need
BMT_Number, etc.
Thanks
dbWhat about looking in the BOL, there is an example for tenaming
columns:
Renaming a column
The following example renames the TerritoryID column in the
SalesTerritory table to TerrID.
Copy Code
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

removing special characters

I have a large table with a text field that includes line break characters. The line break characters are wreaking havoc on other applications, and they need to be removed and replaced with something more friendly. I found a referenced to a stored procedure that can search and replace all strings in a database, but it isn't quite what I need to do, since it only accepts valid keyboard input and not special characters.

Anyone got any suggestions?You should be able to submit the ASCII value of the special character you want to target.

blindman|||If your text field is a varchar or nvarchar you can simply replace the line breaks when selecting the data from the database.

I.E.

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

Hope this helps..|||can u post some example so that it can be tested and answered!!!!!!!!!!|||Originally posted by smorton
If your text field is a varchar or nvarchar you can simply replace the line breaks when selecting the data from the database.

I.E.

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

Hope this helps..

These are TEXT fields, unfortunately.

Next question: if I can enter the ascii value of the character in the find/replace SP, how do I do so?|||That's what this code is doing for you:

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

The CHAR() function converts and integer value to an ASCII character that the REPLACE function can accept. Some common ASCII values are:
Tab: CHAR(9)
Line feed: CHAR(10)
Carriage return: CHAR(13)

If your data is in TEXT format, you may need to use the CAST function to convert it to VARCHAR first:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

blindman|||Originally posted by blindman
That's what this code is doing for you:

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

The CHAR() function converts and integer value to an ASCII character that the REPLACE function can accept. Some common ASCII values are:
Tab: CHAR(9)
Line feed: CHAR(10)
Carriage return: CHAR(13)

If your data is in TEXT format, you may need to use the CAST function to convert it to VARCHAR first:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

blindman

I've been instructed to actually copy this table to an Access database on my desktop, where changes I make won't affect any other applications. So, I need to find an Access solution to the same problem. I'm completely clueless when it comes to Access, s I will probably be doing a LOT of research!|||Originally posted by rscrawford
I've been instructed to actually copy this table to an Access database on my desktop, where changes I make won't affect any other applications. So, I need to find an Access solution to the same problem. I'm completely clueless when it comes to Access, s I will probably be doing a LOT of research!

Kinda like going in the wrong direction...can't they just create a table for you or a small database for you on sql server?

Access was (2002 may be don't know) as feature rich as sql server...|||Originally posted by Brett Kaiser
Kinda like going in the wrong direction...can't they just create a table for you or a small database for you on sql server?


You're right, of course. I duplicated the table in question.

Now, forgive me for being a newbie, but... I have the SELECT statement from blindman:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

How do I execute this? I've opened the Query Analyzer, but I don't see where this statement allows for a FROM clause.

Bear in mind, I'm not at all used to SQL Server. Normally, I'd spend a few hours researching on the web, but this particular piece has a priority to it.|||SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

Replace @.StringToBeSearched with the column name then add your from clause.

I.E.

SELECT REPLACE(REPLACE(Cast(t.ColumnName as varchar(8000)),Char(10),' '),Char(13), '') AS 'ConvertedText'
FROM TableName t

If your column is varchar or nvarchar you do not need to cast the column. If the column is text or ntext the above cast is valid.|||SELECT REPLACE(REPLACE(Cast(t.ColumnName as varchar(8000)),Char(10),' '),Char(13), '') AS 'ConvertedText'
FROM TableName t


Thank you very much!!! That did exactly what I needed it to do!

Removing spaces in Varchar 2000 field

I have a data field defined as Varchar 2000.
I need to remove all spaces greater than 2 in length from the inside of the data. I can remove beginning and ending spaces.

Data Example:
"AAAAAAAAAAAA BBBBB CCCCCC DDDD EEEEEEE"

Need this:
"AAAAAAAAAAAA BBBBB CCCCCC DDDD EEEEEEE"

Can anyone help?The data didn't print like I typed it. Need to remove all spaces in middle of data that are equal to 2 in a row or more.|||Originally posted by mikeinoxford
The data didn't print like I typed it. Need to remove all spaces in middle of data that are equal to 2 in a row or more.

Let say 1=space

select replace('qw11er11tt','11 ','1')|||I will test that. I'm currently working on a process using 'REPLACE'|||One problem is that I don't know the number of spaces. The number of spaces could be 2 or 50. Working on process to 'clean' out all concessive spaces with length of 2 or more.|||I have the same problem|||And the key here is that you want to retain 1 space between each "word".

Yes?|||This probably isn't perfect, but it works. Note that the last (2) replace occurs twice, that is important.DECLARE @.c1 VARCHAR(20)
, @.c2 VARCHAR(20)
, @.c4 VARCHAR(20)
, @.c8 VARCHAR(20)
, @.c16 VARCHAR(20)
, @.cDemo VARCHAR(128)

SELECT @.c1 = Space(1)
, @.c2 = Space(2)
, @.c4 = Space(4)
, @.c8 = Space(8)
, @.c16 = Space(16)
, @.cDemo = 'This is a test of the ability to'
+ ' remove spaces.'

SELECT Replace(Replace(Replace(Replace(Replace(@.cDemo, @.c16, @.c1)
, @.c8, @.c1), @.c4, @.c1), @.c2, @.c1), @.c2, @.c1)-PatP|||I'm stuck...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 varchar(8000))
GO

INSERT INTO myTable99(Col1)
SELECT 'A quick Brown Fox jumps over the lazy dog' UNION ALL
SELECT 'A quick Brown Fox jumps over the lazy dog'
GO

SELECT REPLACE(REPLACE(Col1,SPACE(2),'@.'),'@. ',' ') AS Col1 FROM myTable99
GO|||And one more

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 varchar(8000))
GO

INSERT INTO myTable99(Col1)
SELECT 'A quick Brown Fox jumps over the lazy dog' UNION ALL
SELECT 'A quick Brown Fox jumps over the lazy' + SPACE (2145) + 'dog'
GO

DECLARE @.c1 VARCHAR(20)
, @.c2 VARCHAR(20)
, @.c4 VARCHAR(20)
, @.c8 VARCHAR(20)
, @.c16 VARCHAR(20)
, @.c32 VARCHAR(20)
, @.c64 VARCHAR(20)
, @.c128 VARCHAR(20)

SELECT @.c1 = Space(1)
, @.c2 = Space(2)
, @.c4 = Space(4)
, @.c8 = Space(8)
, @.c16 = Space(16)

SELECT Replace(Replace(Replace(Replace(Replace(Replace(Co l1, @.c16, @.c1)
, @.c8, @.c1), @.c4, @.c1), @.c2, @.c1), @.c2, @.c1), @.c2, @.c1) FROM myTable99
GO

There's gotta be a better way...|||There's gotta be a better way...I don't know of any better way using pure Transact-SQL. While it is ugly, this really is pretty fast and not obnoxiously difficult to code.

-PatP|||One problem is that I don't know the number of spaces. The number of spaces could be 2 or 50. Working on process to 'clean' out all concessive spaces with length of 2 or more.

So if there are three consecutive spaces, do you want them ALL removed, or did you want to leave one space as a separator?|||...and is this a one type deal or part of a repeatable process? Are you looking at actually modifying the underlying data, or just removing the spaces for display. 'Cause its not clear here even what type of code is appropriate; View, Stored Procedure, User-defined function?

Here is one method that cleans all but one intervening space from the data in the table:

SET NOCOUNT ON
CREATE TABLE #myTable99(Col1 varchar(8000))
GO

INSERT INTO #myTable99(Col1)
SELECT 'A quick Brown Fox jumps over the lazy dog'
UNION ALL
SELECT 'A quick Brown Fox jumps over the lazy' + SPACE (2145) + 'dog'

update #myTable99
set Col1 = replace(Col1, ' ', ' ')
where charindex(' ', Col1) > 0

while @.@.RowCount > 0
update #myTable99
set Col1 = replace(Col1, ' ', ' ')
where charindex(' ', Col1) > 0

select * from #myTable99

drop table #myTable99|||I steel have problem

I have table with nvarchar and numbers in it.
It's imposible to convert it to int so I was thinking about spaces behand the number bat the resoliution you showed dosn't works in this case.

So I'm thinking about function (maybe something els) which will take only my numbers which i can convert to int.

Do you have any idea|||I import the table from XLS. and in XLS it looks like space behind the number.|||Not the same problem. Try using RTRIM to get rid of trailing blanks and/or LTRIM to get rid of leading ones.|||That's a different problem than the one that I tried to solve earlier, even though the first problem was amusing!

I'd use something like the following, after you modify it to suit your schema and data:SELECT *
FROM myTable
WHERE 0 = IsNumeric(myColumn)-PatP|||Anyone want to post some sample DDL and DML and expected results like we did...

My telepathic infrared port is not working right now...

My Guess...you got one column that is space delimited with numbers..yes?

You need a UDF to parse it out then strip the numbers and place them in their own columns in another table...|||EDIT: And you should really start a new thread instead of hichjacking this one...|||Absolutely Rafala. Do NOT tag your issue onto the end of this thread. It is WAY too confusing trying to keep track of which problem to solve.|||Would the looping on this be too much?

declare @.str varchar(100)

set @.str = 'this is a test of some code'

select @.str

while charindex (' ', @.str) > 0
begin
select @.str = replace (@.str, ' ', ' ')
end

select @.str

Can't really apply that to a column, though...|||Don't know what this is like perormance wise (performance hit of recusive function calling and both LEN function calls)...but it will stop once it finds no more spaces to replace

CREATE FUNCTION dbo.RecursiveReplace(@.TheString VARCHAR(2000)) RETURNS VARCHAR(2000) AS
BEGIN
DECLARE @.Result VARCHAR(2000);

SET @.Result = REPLACE(@.TheString, ' ', ' ');

IF LEN(@.Result) <> LEN(@.TheString)
SET @.Result = dbo.RecursiveReplace(@.Result);

RETURN @.Result;
END|||I have figured a problem with REPLACE function.

"the space" I have copied from XLS file.|||I mean solved the problem.

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!Look at the hex values of one of the SSN columns that seems to have spaces in it. I'd bet that those aren't really spaces, but another character that looks like a space (in that font, it has the same glyph as a space).

To see the hex, use something like:SELECT Cast(SSN AS VARBINARY(20))
FROM DefendantCase
WHERE -- you figure out what rows interest you-PatP|||...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, '-','')The sample code you gave will do exactly what you wanted it to do, and would not have inserted spaces where the dashes were:select REPLACE('123-45-6789', '-','')So something else is going on. Either you have dirty characters in your data, or you ran a different statement than the one you posted.|||Look at the hex values of one of the SSN columns that seems to have spaces in it. I'd bet that those aren't really spaces, but another character that looks like a space (in that font, it has the same glyph as a space).

To see the hex, use something like:SELECT Cast(SSN AS VARBINARY(20))
FROM DefendantCase
WHERE -- you figure out what rows interest you-PatP
okay, i ran your code and i'm getting this in the results: 0x20
Is that a space?|||This sounds familiar. Wasn't there something back in the 6.5 to 7 upgrade that mentioned that 6.5 allowed empty strings and 7.0 would henceforth treat an empty string as a single space?

You could opt for string manipulation ... something like this:


update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,9,4)
ELSE SSN
end


The usual warnings about saving the data before you update it. I usually extract it to a holding table along with the PK of the row, modify it in the holding table, then apply the changed column back to the production table using a psuedo-cursor loop. It prevents locking up the table wioth massive updates ... plus gives you a rollback path if needed.|||This sounds familiar. Wasn't there something back in the 6.5 to 7 upgrade that mentioned that 6.5 allowed empty strings and 7.0 would henceforth treat an empty string as a single space?

You could opt for string manipulation ... something like this:


update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,9,4)
ELSE SSN
end


The usual warnings about saving the data before you update it. I usually extract it to a holding table along with the PK of the row, modify it in the holding table, then apply the changed column back to the production table using a psuedo-cursor loop. It prevents locking up the table wioth massive updates ... plus gives you a rollback path if needed.
thanks for your post! your solution worked perfectly except i had to use this:

update DefendantCase
SET SSN =
case
when datalength(SSN) = 11
THEN substring(SSN,1,3)+substring(SSN,5,2)+substring(SS N,8,4)
ELSE SSN
end

i had to insert an 8 b/c the last 4 digits of a ssn start at 8. but thanks for the help though! you solved my problem perfectly!

Removing Rich Text formatting from a field

I have a field where the data is stored with RTF. I know SQL Reporting Services cannot display it properly. Is there way to remove the RTF formatting codes through a function to just pull out the text and displya it in the text box?

I had a similiar problem and found this link helpful:

http://www.4guysfromrolla.com/webtech/042501-1.shtml

I just took the function provided in the above link and placed it in the Code section of my report. It may need some tweaking depending on your specific needs but hopefully it will help.

|||

place this code in your Code section:

function StripRTF(ByVal rtf as String) as String
rtb.Rtf = rtf
return rtb.Text
end function

Add a reference to the System.Windows.Forms namespace and create a class instance of System.Windows.Forms.RichTextBox with an instance name of rtb

|||

Hi,

Code Snippet

function StripRTF(ByVal rtf as String) as String
rtb.Rtf = rtf
return rtb.Text
end function

I've similar problem and the above function works in Report Designer. However, when it is deployed in ReportServer, it didn't work at all.

Is there something we need to do in the Report Server?

For your info, both Report Designer and Report Server sits in different machine.

Please help.

Regards

Kamal

|||I am having the same issue. The code works fine in report designer, but out on the report server I get "#Error" in place of what is supposed to be the parsed text.

Ideas?

Thanks in advance

Removing Rich Text formatting from a field

I have a field where the data is stored with RTF. I know SQL Reporting Services cannot display it properly. Is there way to remove the RTF formatting codes through a function to just pull out the text and displya it in the text box?

I had a similiar problem and found this link helpful:

http://www.4guysfromrolla.com/webtech/042501-1.shtml

I just took the function provided in the above link and placed it in the Code section of my report. It may need some tweaking depending on your specific needs but hopefully it will help.

|||

place this code in your Code section:

function StripRTF(ByVal rtf as String) as String
rtb.Rtf = rtf
return rtb.Text
end function

Add a reference to the System.Windows.Forms namespace and create a class instance of System.Windows.Forms.RichTextBox with an instance name of rtb

|||

Hi,

Code Snippet

function StripRTF(ByVal rtf as String) as String
rtb.Rtf = rtf
return rtb.Text
end function

I've similar problem and the above function works in Report Designer. However, when it is deployed in ReportServer, it didn't work at all.

Is there something we need to do in the Report Server?

For your info, both Report Designer and Report Server sits in different machine.

Please help.

Regards

Kamal

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 frontTry this to get the lastname:
lastname = left(column1, patindex('%,%', column1) - 1)|||The error message was :-

update Agency
set lastname = left(lastname, patindex('%,%', lastname) - 1)

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
The statement has been terminated.

Dario D'Alessandro, Dario
Stan Dickson, Stan
Ray John, Ray
Juan Sanchez, Juan

I trying to remove the Dario|||Gotta make sure there is at least one comma, or it errors out. Try this:

set lastname = left(lastname, charindex(',', lastname + ',') - 1)|||Thanks that did actually work.. not sure how that charindex worked. i was using to REPLACE statements to make it work. It was more efficient using your method.|||If you need a method of parsing name strings into component parts, I have a UDF that will do that too.

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
ENDSET @.Max = (SELECT Max(DTect_Supplier_SRN) FROM SUPPLIER_TABLE_TEST)

Change it to count ...don't have much time now,check for other errors.|||Cheers, its all sorted now.

Removing Field Creation Limitations in a BCM SQL Database

I have an Outlook 2007 Business Contact Manger Database on a SBS 2003 R2
Premium Server with SQL 2005.
Whenever I try to add more than 40 User-Defined Fields in BCM I get an error
message stating I can't create more than 40 User-Defined Fields. That's a
huge limitation. Is there a way I can remove that limitation in SQL Server
2005 or at least increase the number?
Thank you.
Hi Rich
I think this is a limit imposed by BCM and not SQL Server as there are 1,024
columns per base table allowed in SQL Server to a maximum row size of 8,060
bytes
John
"Rich" wrote:

> I have an Outlook 2007 Business Contact Manger Database on a SBS 2003 R2
> Premium Server with SQL 2005.
> Whenever I try to add more than 40 User-Defined Fields in BCM I get an error
> message stating I can't create more than 40 User-Defined Fields. That's a
> huge limitation. Is there a way I can remove that limitation in SQL Server
> 2005 or at least increase the number?
> Thank you.
|||Actually in 2005 the row size is really not limited to 8060 bytes any more.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D35CAB96-5D70-4258-AC92-C628E780B95B@.microsoft.com...[vbcol=seagreen]
> Hi Rich
> I think this is a limit imposed by BCM and not SQL Server as there are
> 1,024
> columns per base table allowed in SQL Server to a maximum row size of
> 8,060
> bytes
> John
> "Rich" wrote:
|||Hi
You could argue that with Text datatype the limit does not exist in SQL
2000, so it was conditional anyhow, but Books online still gives it as the
limit in SQL 2005.
It still doesn't explain why there is a seemingly arbitrary limit imposed by
BCM.
John
"Andrew J. Kelly" wrote:

> Actually in 2005 the row size is really not limited to 8060 bytes any more.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D35CAB96-5D70-4258-AC92-C628E780B95B@.microsoft.com...
>
|||If BCM is dependent on SQL 2005 then I would think that there is a way to
increase the limit. Could the SQL database have code in it that commands it
to have a limit in BCM?
If a Microsoft engineer could help put some light on this I would greatly
appreciate it.
Thank you.
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You could argue that with Text datatype the limit does not exist in SQL
> 2000, so it was conditional anyhow, but Books online still gives it as the
> limit in SQL 2005.
> It still doesn't explain why there is a seemingly arbitrary limit imposed by
> BCM.
> John
>
> "Andrew J. Kelly" wrote:
|||I don't see any BCM MSDN group. Because this isn't a break/fix issue I can't
post it to the Microsoft Support Managed Newsgroup. I've tried that and they
said to post in the MSDN Newsgroup.
"Tibor Karaszi" wrote:

> I suggest you ask this in a BCM group, since the limit is in the application (BCM), not in SQL
> Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:4DA60BC0-5942-45EA-A668-DF2D412FF791@.microsoft.com...
>

Removing Field Creation Limitations in a BCM SQL Database

I have an Outlook 2007 Business Contact Manger Database on a SBS 2003 R2
Premium Server with SQL 2005.
Whenever I try to add more than 40 User-Defined Fields in BCM I get an error
message stating I can't create more than 40 User-Defined Fields. That's a
huge limitation. Is there a way I can remove that limitation in SQL Server
2005 or at least increase the number?
Thank you.Hi Rich
I think this is a limit imposed by BCM and not SQL Server as there are 1,024
columns per base table allowed in SQL Server to a maximum row size of 8,060
bytes
John
"Rich" wrote:
> I have an Outlook 2007 Business Contact Manger Database on a SBS 2003 R2
> Premium Server with SQL 2005.
> Whenever I try to add more than 40 User-Defined Fields in BCM I get an error
> message stating I can't create more than 40 User-Defined Fields. That's a
> huge limitation. Is there a way I can remove that limitation in SQL Server
> 2005 or at least increase the number?
> Thank you.|||Actually in 2005 the row size is really not limited to 8060 bytes any more.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D35CAB96-5D70-4258-AC92-C628E780B95B@.microsoft.com...
> Hi Rich
> I think this is a limit imposed by BCM and not SQL Server as there are
> 1,024
> columns per base table allowed in SQL Server to a maximum row size of
> 8,060
> bytes
> John
> "Rich" wrote:
>> I have an Outlook 2007 Business Contact Manger Database on a SBS 2003 R2
>> Premium Server with SQL 2005.
>> Whenever I try to add more than 40 User-Defined Fields in BCM I get an
>> error
>> message stating I can't create more than 40 User-Defined Fields. That's
>> a
>> huge limitation. Is there a way I can remove that limitation in SQL
>> Server
>> 2005 or at least increase the number?
>> Thank you.|||Hi
You could argue that with Text datatype the limit does not exist in SQL
2000, so it was conditional anyhow, but Books online still gives it as the
limit in SQL 2005.
It still doesn't explain why there is a seemingly arbitrary limit imposed by
BCM.
John
"Andrew J. Kelly" wrote:
> Actually in 2005 the row size is really not limited to 8060 bytes any more.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D35CAB96-5D70-4258-AC92-C628E780B95B@.microsoft.com...
> > Hi Rich
> >
> > I think this is a limit imposed by BCM and not SQL Server as there are
> > 1,024
> > columns per base table allowed in SQL Server to a maximum row size of
> > 8,060
> > bytes
> >
> > John
> >
> > "Rich" wrote:
> >
> >> I have an Outlook 2007 Business Contact Manger Database on a SBS 2003 R2
> >> Premium Server with SQL 2005.
> >>
> >> Whenever I try to add more than 40 User-Defined Fields in BCM I get an
> >> error
> >> message stating I can't create more than 40 User-Defined Fields. That's
> >> a
> >> huge limitation. Is there a way I can remove that limitation in SQL
> >> Server
> >> 2005 or at least increase the number?
> >>
> >> Thank you.
>|||If BCM is dependent on SQL 2005 then I would think that there is a way to
increase the limit. Could the SQL database have code in it that commands it
to have a limit in BCM?
If a Microsoft engineer could help put some light on this I would greatly
appreciate it.
Thank you.
"John Bell" wrote:
> Hi
> You could argue that with Text datatype the limit does not exist in SQL
> 2000, so it was conditional anyhow, but Books online still gives it as the
> limit in SQL 2005.
> It still doesn't explain why there is a seemingly arbitrary limit imposed by
> BCM.
> John
>
> "Andrew J. Kelly" wrote:
> > Actually in 2005 the row size is really not limited to 8060 bytes any more.
> >
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> >
> >
> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > news:D35CAB96-5D70-4258-AC92-C628E780B95B@.microsoft.com...
> > > Hi Rich
> > >
> > > I think this is a limit imposed by BCM and not SQL Server as there are
> > > 1,024
> > > columns per base table allowed in SQL Server to a maximum row size of
> > > 8,060
> > > bytes
> > >
> > > John
> > >
> > > "Rich" wrote:
> > >
> > >> I have an Outlook 2007 Business Contact Manger Database on a SBS 2003 R2
> > >> Premium Server with SQL 2005.
> > >>
> > >> Whenever I try to add more than 40 User-Defined Fields in BCM I get an
> > >> error
> > >> message stating I can't create more than 40 User-Defined Fields. That's
> > >> a
> > >> huge limitation. Is there a way I can remove that limitation in SQL
> > >> Server
> > >> 2005 or at least increase the number?
> > >>
> > >> Thank you.
> >
> >|||> Could the SQL database have code in it that commands it
> to have a limit in BCM?
> If a Microsoft engineer could help put some light on this I would greatly
> appreciate it.
I suggest you ask this in a BCM group, since the limit is in the application (BCM), not in SQL
Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:4DA60BC0-5942-45EA-A668-DF2D412FF791@.microsoft.com...
> If BCM is dependent on SQL 2005 then I would think that there is a way to
> increase the limit. Could the SQL database have code in it that commands it
> to have a limit in BCM?
> If a Microsoft engineer could help put some light on this I would greatly
> appreciate it.
> Thank you.
> "John Bell" wrote:
>> Hi
>> You could argue that with Text datatype the limit does not exist in SQL
>> 2000, so it was conditional anyhow, but Books online still gives it as the
>> limit in SQL 2005.
>> It still doesn't explain why there is a seemingly arbitrary limit imposed by
>> BCM.
>> John
>>
>> "Andrew J. Kelly" wrote:
>> > Actually in 2005 the row size is really not limited to 8060 bytes any more.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> > Solid Quality Mentors
>> >
>> >
>> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> > news:D35CAB96-5D70-4258-AC92-C628E780B95B@.microsoft.com...
>> > > Hi Rich
>> > >
>> > > I think this is a limit imposed by BCM and not SQL Server as there are
>> > > 1,024
>> > > columns per base table allowed in SQL Server to a maximum row size of
>> > > 8,060
>> > > bytes
>> > >
>> > > John
>> > >
>> > > "Rich" wrote:
>> > >
>> > >> I have an Outlook 2007 Business Contact Manger Database on a SBS 2003 R2
>> > >> Premium Server with SQL 2005.
>> > >>
>> > >> Whenever I try to add more than 40 User-Defined Fields in BCM I get an
>> > >> error
>> > >> message stating I can't create more than 40 User-Defined Fields. That's
>> > >> a
>> > >> huge limitation. Is there a way I can remove that limitation in SQL
>> > >> Server
>> > >> 2005 or at least increase the number?
>> > >>
>> > >> Thank you.
>> >
>> >|||I don't see any BCM MSDN group. Because this isn't a break/fix issue I can't
post it to the Microsoft Support Managed Newsgroup. I've tried that and they
said to post in the MSDN Newsgroup.
"Tibor Karaszi" wrote:
> > Could the SQL database have code in it that commands it
> > to have a limit in BCM?
> >
> > If a Microsoft engineer could help put some light on this I would greatly
> > appreciate it.
> I suggest you ask this in a BCM group, since the limit is in the application (BCM), not in SQL
> Server.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:4DA60BC0-5942-45EA-A668-DF2D412FF791@.microsoft.com...
> > If BCM is dependent on SQL 2005 then I would think that there is a way to
> > increase the limit. Could the SQL database have code in it that commands it
> > to have a limit in BCM?
> >
> > If a Microsoft engineer could help put some light on this I would greatly
> > appreciate it.
> >
> > Thank you.
> >
> > "John Bell" wrote:
> >
> >> Hi
> >>
> >> You could argue that with Text datatype the limit does not exist in SQL
> >> 2000, so it was conditional anyhow, but Books online still gives it as the
> >> limit in SQL 2005.
> >>
> >> It still doesn't explain why there is a seemingly arbitrary limit imposed by
> >> BCM.
> >>
> >> John
> >>
> >>
> >> "Andrew J. Kelly" wrote:
> >>
> >> > Actually in 2005 the row size is really not limited to 8060 bytes any more.
> >> >
> >> > --
> >> > Andrew J. Kelly SQL MVP
> >> > Solid Quality Mentors
> >> >
> >> >
> >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> > news:D35CAB96-5D70-4258-AC92-C628E780B95B@.microsoft.com...
> >> > > Hi Rich
> >> > >
> >> > > I think this is a limit imposed by BCM and not SQL Server as there are
> >> > > 1,024
> >> > > columns per base table allowed in SQL Server to a maximum row size of
> >> > > 8,060
> >> > > bytes
> >> > >
> >> > > John
> >> > >
> >> > > "Rich" wrote:
> >> > >
> >> > >> I have an Outlook 2007 Business Contact Manger Database on a SBS 2003 R2
> >> > >> Premium Server with SQL 2005.
> >> > >>
> >> > >> Whenever I try to add more than 40 User-Defined Fields in BCM I get an
> >> > >> error
> >> > >> message stating I can't create more than 40 User-Defined Fields. That's
> >> > >> a
> >> > >> huge limitation. Is there a way I can remove that limitation in SQL
> >> > >> Server
> >> > >> 2005 or at least increase the number?
> >> > >>
> >> > >> Thank you.
> >> >
> >> >
>|||Hi Rich
I believe BCM is part of the MS Office family microsoft.public.outlook.bcm,
although I would not raise your hopes by suggesting you posted to the office
news group, but it would be worth a try. There is some online help at
http://office.microsoft.com/en-gb/outlook/FX100647191033.aspx?CTT=96&Origin=CL100626971033
Any unsanctioned change may invalidate your licence agreement, therefore, if
failing to get a suitable response I can only suggest that you would need to
open an issue with product support
http://support.microsoft.com/oas/default.aspx?&gprid=8753&
John
"Rich" wrote:
> I don't see any BCM MSDN group. Because this isn't a break/fix issue I can't
> post it to the Microsoft Support Managed Newsgroup. I've tried that and they
> said to post in the MSDN Newsgroup.
> "Tibor Karaszi" wrote:
> > > Could the SQL database have code in it that commands it
> > > to have a limit in BCM?
> > >
> > > If a Microsoft engineer could help put some light on this I would greatly
> > > appreciate it.
> >
> > I suggest you ask this in a BCM group, since the limit is in the application (BCM), not in SQL
> > Server.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Rich" <Rich@.discussions.microsoft.com> wrote in message
> > news:4DA60BC0-5942-45EA-A668-DF2D412FF791@.microsoft.com...
> > > If BCM is dependent on SQL 2005 then I would think that there is a way to
> > > increase the limit. Could the SQL database have code in it that commands it
> > > to have a limit in BCM?
> > >
> > > If a Microsoft engineer could help put some light on this I would greatly
> > > appreciate it.
> > >
> > > Thank you.
> > >
> > > "John Bell" wrote:
> > >
> > >> Hi
> > >>
> > >> You could argue that with Text datatype the limit does not exist in SQL
> > >> 2000, so it was conditional anyhow, but Books online still gives it as the
> > >> limit in SQL 2005.
> > >>
> > >> It still doesn't explain why there is a seemingly arbitrary limit imposed by
> > >> BCM.
> > >>
> > >> John
> > >>
> > >>
> > >> "Andrew J. Kelly" wrote:
> > >>
> > >> > Actually in 2005 the row size is really not limited to 8060 bytes any more.
> > >> >
> > >> > --
> > >> > Andrew J. Kelly SQL MVP
> > >> > Solid Quality Mentors
> > >> >
> > >> >
> > >> > "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> > >> > news:D35CAB96-5D70-4258-AC92-C628E780B95B@.microsoft.com...
> > >> > > Hi Rich
> > >> > >
> > >> > > I think this is a limit imposed by BCM and not SQL Server as there are
> > >> > > 1,024
> > >> > > columns per base table allowed in SQL Server to a maximum row size of
> > >> > > 8,060
> > >> > > bytes
> > >> > >
> > >> > > John
> > >> > >
> > >> > > "Rich" wrote:
> > >> > >
> > >> > >> I have an Outlook 2007 Business Contact Manger Database on a SBS 2003 R2
> > >> > >> Premium Server with SQL 2005.
> > >> > >>
> > >> > >> Whenever I try to add more than 40 User-Defined Fields in BCM I get an
> > >> > >> error
> > >> > >> message stating I can't create more than 40 User-Defined Fields. That's
> > >> > >> a
> > >> > >> huge limitation. Is there a way I can remove that limitation in SQL
> > >> > >> Server
> > >> > >> 2005 or at least increase the number?
> > >> > >>
> > >> > >> Thank you.
> > >> >
> > >> >
> >
> >

removing embedded duplicate rows

I have a table that contains a field with data like:
dog 1
dog 2
cat 1
cat 2
I only what to return one of the values. DISTINCT doesn't do it. Any
suggestions?
Thanks,
BobWhich value do you want? Distinct rolls up multiple values that are the
same. Your four values are all different, and thus cannot be rolled up.
Describe what you expect to see from the sample data you quoted.
More info, please.|||Assuming table below with 2 cols name & id
name id
-- --
dog 1
dog 2
cat 1
cat 2
-- following will get u the 1st of every grp with same name
select name, id
from t
where id in (select min(id) from t group by name)
Rakesh
"bday55" wrote:

> I have a table that contains a field with data like:
> dog 1
> dog 2
> cat 1
> cat 2
> I only what to return one of the values. DISTINCT doesn't do it. Any
> suggestions?
> Thanks,
> Bob
>|||On Wed, 10 Aug 2005 22:51:01 -0700, Rakesh wrote:

>Assuming table below with 2 cols name & id
>name id
>-- --
>dog 1
>dog 2
>cat 1
>cat 2
>-- following will get u the 1st of every grp with same name
>select name, id
>from t
>where id in (select min(id) from t group by name)
Hi Rakesh,
But that won't work if the rows are changed to

>name id
>-- --
>dog 1
>dog 2
>cat 2
>cat 3
Here's a better one:
SELECT name, id
FROM t AS t1
WHERE id = (SELECT MIN(id) FROM t AS t2 WHERE t1.name = t2.name)
Or, even simpler:
SELECT name, MIN(id)
FROM t
GROUP BY t
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Removing Duplicate Values in Filter List...

Hello!
I am pulling two values from a field when the report is first run. The
value can either be ACTIVE or DISABLED. This is being accessed in a
parameter and I would like to be able to filter my current report by
selecting either ACTIVE or DISABLED records. The catch is that I need to do
this without taking a trip back to the server.
I could hardcode the two values in there, however that would require the
report to re-render each time the filter is applied. Can I have two distinct
values in my filter and have this applied at the time of the first rendering?
Right now I get all values for my status_cd column which is a bunch of
DISABLED and ACTIVE values. I just need two. Let me know what you think.
Thanks!Just a short clarification. After I wrote all that I realized that I needed
to explain a little more...
We are pulling in the values for the filter from a dataset. The
Active/Disabled example isn't all that great because I can hardcode the
values for that to get it to work. However, the functionality we really need
is as follows...
We have users that are associated with clients. So multiple users have the
same client. In this filter drop-down when it populates from the dataset it
is populating the same client multiple times based upon how many users are
associated with that client. All we need is each client a single time. We
can do this by creating another dataset, but we would like to do it with the
current dataset instead of making a trip back to the server. Your help is
appreciated. Thanks!
"jrennard" wrote:
> Hello!
> I am pulling two values from a field when the report is first run. The
> value can either be ACTIVE or DISABLED. This is being accessed in a
> parameter and I would like to be able to filter my current report by
> selecting either ACTIVE or DISABLED records. The catch is that I need to do
> this without taking a trip back to the server.
> I could hardcode the two values in there, however that would require the
> report to re-render each time the filter is applied. Can I have two distinct
> values in my filter and have this applied at the time of the first rendering?
> Right now I get all values for my status_cd column which is a bunch of
> DISABLED and ACTIVE values. I just need two. Let me know what you think.
> Thanks!

removing duplicate entries in SQL Field

Hi All,

Below is a snippet of MS SQL inside some VB that retieves
Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get green, red, blue, and yellow which is correct. On another similar search with different commodity say for material, I get Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic, Glass, Sand. I want to remove the repeating elements returned in this field. IOW, I just need one set of Plastic, Glass and Sand. I hope this makes sense.

Below is the SQL and the results from the returned page.

PS I tried to use distinct but with no luck I want just one of each in the example below.

Thanks in Advance!

Scott

==============================

SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "

SQL1 = ""
SQL1 = SQL

SQL = SQL & "WHERE " & Where & " "
==================================

Here is a piece of the problem field, note repeating colors etc.

CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11

Hi,

if you are using denormalized stored values, you will first have denormalized them to use SQL Server functions for grouping / distinct them. You will have to separate the values first using a separator and using a function like this one here:

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Removing duplicate dat

Hi
I have a 1.5 million row table with a text field. Can
anybody think of a way to remove any duplicate rows where the text field
contains the same data (and a
way which will not take days to run!)
For example if I have two rows with "SQL SERVER IS GREAT", I want to remove
one of them.
Thanks
here is an example of something that works for me:
create table textstuff
(pk int not null primary key,
textcol text)
go
--insert statements
declare @.int int
select @.int=max(datalength(textcol)) from textstuff
select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
textstuff order by 2
select pk, holding.checksum from holding,
(select checksum, test=count(checksum) from holding group by checksum having
count(checksum) >1) as a
where holding.checksum=a.checksum
rows which have identical checksums will show up here.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul" <xx@.nospam.com> wrote in message
news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi
> I have a 1.5 million row table with a text field. Can
> anybody think of a way to remove any duplicate rows where the text field
> contains the same data (and a
> way which will not take days to run!)
> For example if I have two rows with "SQL SERVER IS GREAT", I want to
remove
> one of them.
> Thanks
>
>
|||That's awsome, thanks Hilary
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u4beTGwuEHA.3808@.TK2MSFTNGP15.phx.gbl...
> here is an example of something that works for me:
> create table textstuff
> (pk int not null primary key,
> textcol text)
> go
> --insert statements
> declare @.int int
> select @.int=max(datalength(textcol)) from textstuff
> select pk, checksum=checksum(substring(textcol,1, @.int)) into holding from
> textstuff order by 2
> select pk, holding.checksum from holding,
> (select checksum, test=count(checksum) from holding group by checksum
having
> count(checksum) >1) as a
> where holding.checksum=a.checksum
>
> rows which have identical checksums will show up here.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Paul" <xx@.nospam.com> wrote in message
> news:e1O%23GfsuEHA.3416@.TK2MSFTNGP09.phx.gbl...
> remove
>

Removing decimals from a nvarchar field

I am trying to remove decimals...for example I have values like this:
01.234.678 and I want to update it to 01234678 or 12345678.
Thanks
SELECT REPLACE(column, '.', '') FROM table;
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:B084D109-B3E8-4D0D-B0BC-CC7D836ACA8D@.microsoft.com...
>I am trying to remove decimals...for example I have values like this:
> 01.234.678 and I want to update it to 01234678 or 12345678.
> Thanks

Removing decimals from a nvarchar field

I am trying to remove decimals...for example I have values like this:
01.234.678 and I want to update it to 01234678 or 12345678.
ThanksSELECT REPLACE(column, '.', '') FROM table;
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:B084D109-B3E8-4D0D-B0BC-CC7D836ACA8D@.microsoft.com...
>I am trying to remove decimals...for example I have values like this:
> 01.234.678 and I want to update it to 01234678 or 12345678.
> Thanks

removing chr(13) and chr(10) from SQL

Hi.

Is there a way to remove carriage returns and line feeds from a field in SQL.

Here is what I have at the moment:

CAST(m.Description AS VARCHAR(8000))AS "Product Description"

(have had to cast as VarChar because of problems with aggregate functions in rest of query).

Problem is that this m.description field has carriage returns and line breaks in it .. how do I remove these inside the SQL statement?

thanksHave you tried REPLACE ( StringExpression1 , StringExpression2 , StringExpression3 ) T-SQL function?|||I tried something like this:

CAST(replace(m.Description, vlbf, ' ') AS VARCHAR(8000))AS "Product Description",

and tried variations of vlbf such as chr(10) chr(13) or \r\n and none of them worked.

I can't put them in single quotes as SQL will look for the actual occurence in the string rather than what it stands for.

I get the error column vlbf is unknown or chr(10) is an unknown function...|||sorted it now.... used this in the end:

REPLACE(CAST(m.Description AS varChar(1000)),char(13) + char(10),' ') AS "Product Description",

Thanks for the help

Removing Characters in Field grouping

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

you can use Rtrim|||

Are you always removing 11 character regardless of the field length or do you simply want to restrict it to a certain size?

For example, the following code restricts the length to 5 characters:

=iif(len(rtrim(Fields!TestDATA.Value)) > 5, mid(rtrim(Fields!TestDATA.Value),1,5), rtrim(Fields!TESTDATA.Value))