|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
space(0) and replace('123-45', '-', '')Hi all.
If anyone can help me please do. What should the following query return. I am getting different results from different servers. replace ('123-45', '-', '') Sometimes I get '123 45'. Othertimes I get '12345'. -- Sam, Ziggy says there's a 81.25% chance that Chelsea will win the Premiership this season. (Probability based on odds on http://www.paddypower.com) What about the collation of that field?
you could compare the collation of these tables for that field in all the servers where happens this. regards, Show quote "loop" wrote: > Hi all. > > If anyone can help me please do. > > What should the following query return. I am getting different > results from different servers. > > replace ('123-45', '-', '') > > Sometimes I get '123 45'. > Othertimes I get '12345'. > > -- > Sam, Ziggy says there's a 81.25% chance that Chelsea > will win the Premiership this season. > > (Probability based on odds on http://www.paddypower.com) > <En***@discussions.microsoft.com> said:
> What about the collation of that field? There are no tables.> you could compare the collation of these tables for that field in all the > servers where happens this. Its just a single statement to manipulate a string. Show quote > regards, > "loop" wrote: > >> Hi all. >> >> If anyone can help me please do. >> >> What should the following query return. I am getting different >> results from different servers. >> >> replace ('123-45', '-', '') >> >> Sometimes I get '123 45'. >> Othertimes I get '12345'. >> >> -- >> Sam, Ziggy says there's a 81.25% chance that Chelsea >> will win the Premiership this season. >> >> (Probability based on odds on http://www.paddypower.com) >> -- Sam, Ziggy says there's a 81.25% chance that Chelsea will win the Premiership this season. (Probability based on odds on http://www.paddypower.com) So against some databases it works differently than others. Are the
databases / servers set up on different codepages? Also, what do these yield in either case? SET NOCOUNT ON DECLARE @foo NVARCHAR(32) SELECT @foo = '123-45' SELECT REPLACE(@foo, '-', '') SELECT REPLACE(@foo, '-', SPACE(0)) SELECT REPLACE(@foo, '-', CONVERT(CHAR(1), '')) SELECT REPLACE(@foo, '-', CONVERT(CHAR(1), SPACE(0))) SELECT REPLACE(@foo, '-', CONVERT(NCHAR(1), '')) SELECT REPLACE(@foo, '-', CONVERT(NCHAR(1), SPACE(0))) SELECT REPLACE(@foo, '-', CONVERT(VARCHAR(1), '')) SELECT REPLACE(@foo, '-', CONVERT(VARCHAR(1), SPACE(0))) SELECT REPLACE(@foo, '-', CONVERT(NVARCHAR(1), '')) SELECT REPLACE(@foo, '-', CONVERT(NVARCHAR(1), SPACE(0))) (Try again with @foo's declaration being VARCHAR instead of NVARCHAR.) Show quote "loop" <AllegraGel***@eXistenZ.com> wrote in message news:slrndlfh2o.m37.AllegraGellar@carbon.redbrick.dcu.ie... > <En***@discussions.microsoft.com> said: >> What about the collation of that field? >> you could compare the collation of these tables for that field in all the >> servers where happens this. > > There are no tables. > > Its just a single statement to manipulate a string. > >> regards, >> "loop" wrote: >> >>> Hi all. >>> >>> If anyone can help me please do. >>> >>> What should the following query return. I am getting different >>> results from different servers. >>> >>> replace ('123-45', '-', '') >>> >>> Sometimes I get '123 45'. >>> Othertimes I get '12345'. >>> >>> -- >>> Sam, Ziggy says there's a 81.25% chance that Chelsea >>> will win the Premiership this season. >>> >>> (Probability based on odds on http://www.paddypower.com) >>> > > > -- > Sam, Ziggy says there's a 81.25% chance that Chelsea > will win the Premiership this season. > > (Probability based on odds on http://www.paddypower.com) Loop,
Same data types for the column used in the REPLACE function? HTH Jerry Show quote "loop" <AllegraGel***@eXistenZ.com> wrote in message news:slrndlfe8h.67b.AllegraGellar@carbon.redbrick.dcu.ie... > Hi all. > > If anyone can help me please do. > > What should the following query return. I am getting different > results from different servers. > > replace ('123-45', '-', '') > > Sometimes I get '123 45'. > Othertimes I get '12345'. > > -- > Sam, Ziggy says there's a 81.25% chance that Chelsea > will win the Premiership this season. > > (Probability based on odds on http://www.paddypower.com) <jspi***@vestas-awt.com> said:
> Loop, I am not using any columns.> > Same data types for the column used in the REPLACE function? > > HTH Just running this statement in SQL Query Analyser Window. select replace('123-45', '-', '') It is not selecting from any columns. Loop,
Compare: 1. SQL version 2. SQL SP version 3. SET settings for the session for both servers. HTH Jerry Show quote "loop" <AllegraGel***@eXistenZ.com> wrote in message news:slrndlfh0n.m37.AllegraGellar@carbon.redbrick.dcu.ie... > <jspi***@vestas-awt.com> said: >> Loop, >> >> Same data types for the column used in the REPLACE function? >> >> HTH > > I am not using any columns. > > Just running this statement in SQL Query Analyser Window. > > select replace('123-45', '-', '') > > It is not selecting from any columns. <AllegraGel***@eXistenZ.com> said:
> And the answer is that the db compatability level is set to> Hi all. > > If anyone can help me please do. > > What should the following query return. I am getting different > results from different servers. > > replace ('123-45', '-', '') > > Sometimes I get '123 45'. > Othertimes I get '12345'. 65 for some and 80 for others. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_5c8c.asp -- Sam, Ziggy says there's a 81.25% chance that Chelsea will win the Premiership this season. (Probability based on odds on http://www.paddypower.com)  |
|||||||||||||||||||||||
Other interesting topics