'REPLACE' increases the size of a text field...?

[Norwich]

Hi,

I'm running a simple view which has one function yo strip out spaces from a postcode. The pertinent part is:

CREATE VIEW dbo.nospace
AS
SELECT CONTACT1.*,
{ fn REPLACE(ZIP, ' ', '') } AS shrunkZIP
FROM CONTACT1

In the table, ZIP is only 10 characters in length, however, in query analyser, the returned shrunkZIP from the view is 255 characters in length. Which is really confusing an access2000 query which is linking to the database.

Any ideas why it may be doing this and ways to bring the length back down again?

Thanks

 

[cbirknh]

I am not sure exactly what is going on here, but maybe you need to explicitly convert the return value to the desired format.
Try:
SELECT CONTACT1.*,
Cast(REPLACE(ZIP, ' ', '') as varchar(10)) AS shrunkZIP
FROM CONTACT1

Chris

 

[SQLBill]

In Query Analyzer you can go to TOOLS > OPTIONS > Results tab and change the "Maximum characters per column" from its default of 255 to whatever you want it to be. However, it will return ALL columns with that length.


-SQLBill

 

[SQLBill]

By the way, you might experiment with SUBSTRING. The syntax is SUBSTRING(expression, start, length).


See the Books OnLine (BOL) for more help.

-SQLBill

 

[Norwich]

Hi,

Thanks for the info - but it appears that cast's use of 'AS' is not allowed in a view - though I'll try and find out if there's a different way to set it up as I'm using the access/msde interface to edit the view and not enterprise manager which I don't have on this machine.

Thanks for the tip on query analyser but I really should have said I was using that as an example, as the problem I'm having is using a similar query within access linking to the view - where I can't make a similar change.

 

[SQLBill]

Ah....ACCESS!!! You might be better off posting the query in the MS Access forum which is a couple of doors down the hall <BIG GRIN>. Seriously, there's lots of folks here who know Access and might be able to help. But you would be better off posting in the proper forum.

-SQLBill