adding to text

If I have a varchar field, I can easily add to it. For instance
select 'Name: ' + fname as fname from Customers.

But what if I have a text field instead of varchar?

select 'Summary: ' + summary as Summary from Customers wont work at all.
Is there a way to accomplish this?TEXT columns really ought to be manipulated on the client, not the server. There are a number of reasons for this, most of which are design and performance issues.

If you really must manipulate a TEXT column on the server, you can use the UPDATETEXT (http://msdn2.microsoft.com/en-us/library/ms189466.aspx) statment, but I'll forewarn you that it is rather ugly.

You really ought to handle this on the client if you can't make the column a VARCHAR instead of a TEXT column.

-PatPsql

Adding new lines into results in the

I used to be able to enter new lines into the result pane cell for text
(and varchar) data in Enterprise Manager, but now that I am using SQL
2005 Management Studio, this feature is gone.

Is there any way to do this?

Also, copying to/from excel chops off part of the text in a cell and is
very infuriating.

Any help would be appreciated.

Dan(monkeyboydan@.gmail.com) writes:
> I used to be able to enter new lines into the result pane cell for text
> (and varchar) data in Enterprise Manager, but now that I am using SQL
> 2005 Management Studio, this feature is gone.
> Is there any way to do this?
> Also, copying to/from excel chops off part of the text in a cell and is
> very infuriating.

Time to learn to write INSERT and UPDATE statements, I see!

There are plenty of differences between the tools in SQL 2000 and SQL 2005.
Keep in mind that Open table is intended to be a fairly simple tool to
view and edit data. For more heavy-duty stuff, you would use an application.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the reply.

Your point is noted and I am quite happy to do insert and update
statements but there are occasions where a little ad-hoc editing and
copying and pasting is useful and this isn't possible any more and it
seems a bit silly 'cause it makes the 'open table' functionality
vritually pointless is a lot of occasions.

Adding data with a query

I have a query that reads ""SELECT [po number], qty, cartons, outtrailer,
shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
and this returns data that falls within the dates specified.
Along with this, I would like to have something that marks another field
(Sent) telling me that the records returned from the above statement, have
been returned.
The purpose is so that I can easily see which records have been returned.UPDATE [tableWhere YouHaveSentColumn_ItCanBeThe_jcrew_IfYouWant] SET Sent =1
FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate
it will update every row where the WHERE CLAUSE apply
--
Bruno Alexandre
(a Portuguese in Denmark)
"Johnfli" <john@.ivhs.us> escreveu na mensagem
news:%23H$5etEeGHA.3888@.TK2MSFTNGP02.phx.gbl...
>I have a query that reads ""SELECT [po number], qty, cartons, outtrailer,
> shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
> tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
> and this returns data that falls within the dates specified.
> Along with this, I would like to have something that marks another field
> (Sent) telling me that the records returned from the above statement, have
> been returned.
> The purpose is so that I can easily see which records have been returned.
>
>

Adding data with a query

I have a query that reads ""SELECT [po number], qty, cartons, outtrailer
,
shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
and this returns data that falls within the dates specified.
Along with this, I would like to have something that marks another field
(Sent) telling me that the records returned from the above statement, have
been returned.
The purpose is so that I can easily see which records have been returned.UPDATE [tableWhere YouHaveSentColumn_ItCanBeThe_jcrew_IfYou
Want] SET Sen
t =
1
FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate
it will update every row where the WHERE CLAUSE apply
Bruno Alexandre
(a Portuguese in Denmark)
"Johnfli" <john@.ivhs.us> escreveu na mensagem
news:%23H$5etEeGHA.3888@.TK2MSFTNGP02.phx.gbl...
>I have a query that reads ""SELECT [po number], qty, cartons, outtraile
r,
> shipdate FROM jcrew WHERE Convert(varchar(10),shipdate,101) LIKE '" &
> tmpMonth & "/" & tmpDay & "%' ORDER BY shipdate""
> and this returns data that falls within the dates specified.
> Along with this, I would like to have something that marks another field
> (Sent) telling me that the records returned from the above statement, have
> been returned.
> The purpose is so that I can easily see which records have been returned.
>
>

Adding another step to the following

I have the following statement :
CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
This runs perfectly.
However I just found out that the field actually contains two different
"types" of data. Example of the two data entries in the field are:
00000011 or
00000-12
What I need to add to the above statement is that if the data in the field
contain the '-', I need for it to be changed to a zero (0) and keep it so
that the results would look like
11 or
012
Thanks in advance... Will post sample data and table if neededOn Wed, 26 Jan 2005 08:07:04 -0800, scuba79 wrote:
(snip)
>What I need to add to the above statement is that if the data in the field
>contain the '-', I need for it to be changed to a zero (0) and keep it so
>that the results would look like
(snip)
Hi scuba79,
You can change any '-' to '0' with
REPLACE (terminatingcountrycode, '-', '0')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try,
select
case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
patindex('%[^0-9]%', reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"scuba79" wrote:

> I have the following statement :
> CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
> CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%',
> TerminatingIDDDCityCode), LEN(TerminatingIDDDCityCode)))))
> This runs perfectly.
> However I just found out that the field actually contains two different
> "types" of data. Example of the two data entries in the field are:
> 00000011 or
> 00000-12
> What I need to add to the above statement is that if the data in the field
> contain the '-', I need for it to be changed to a zero (0) and keep it so
> that the results would look like
> 11 or
> 012
> Thanks in advance... Will post sample data and table if needed
>|||Or,
select
case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
reverse(colA)) - 1)
else cast(cast(colA as int) as varchar)
end
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> case when patindex('%[^0-9]%', colA) > 0 then '0' + right(colA,
> patindex('%[^0-9]%', reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "scuba79" wrote:
>|||Or,
select
replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
cast(cast(colA as int) as varchar)), '-', '0')
from
(
select '00000011'
union all
select '00000-12'
) as t(colA)
go
AMB
"Alejandro Mesa" wrote:
> Or,
> select
> case when charindex('-', colA) > 0 then '0' + right(colA, charindex('-',
> reverse(colA)) - 1)
> else cast(cast(colA as int) as varchar)
> end
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
> "Alejandro Mesa" wrote:
>|||Alejandro,
Your statement works great when I run it as a seperate statement and not
combined with the rest of the statement that I provided. I mistated my
question... The statement shows that there are two different fields being
changed. However, it's the field "terminatingidddcitycode" that has the two
different data entries that I need to find the solution for while still bein
g
able to use
"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
part, since that field will never contain any "-". Unless I totaling losing
it and not understanding your code
Thanks
scuba79
"Alejandro Mesa" wrote:
> Or,
> select
> replace(coalesce(nullif(right(colA, charindex('-', reverse(colA))), ''),
> cast(cast(colA as int) as varchar)), '-', '0')
> from
> (
> select '00000011'
> union all
> select '00000-12'
> ) as t(colA)
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Can you provide some DDL (for those columns), sample data and expected resul
t?
AMB
"scuba79" wrote:
> Alejandro,
> Your statement works great when I run it as a seperate statement and not
> combined with the rest of the statement that I provided. I mistated my
> question... The statement shows that there are two different fields being
> changed. However, it's the field "terminatingidddcitycode" that has the t
wo
> different data entries that I need to find the solution for while still be
ing
> able to use
> "CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
> PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+
"
> part, since that field will never contain any "-". Unless I totaling losi
ng
> it and not understanding your code
> Thanks
> scuba79
> "Alejandro Mesa" wrote:
>|||On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:

>Your statement works great when I run it as a seperate statement and not
>combined with the rest of the statement that I provided. I mistated my
>question... The statement shows that there are two different fields being
>changed. However, it's the field "terminatingidddcitycode" that has the tw
o
>different data entries that I need to find the solution for while still bei
ng
>able to use
>"CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode,
>PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+"
>part, since that field will never contain any "-". Unless I totaling losin
g
>it and not understanding your code
Hi scuba79,
If you take your original query and change each terminatingidddcitycode to
REPLACE (terminatingidddcitycode, '-','0'), it should work.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
By using the replace statement as you stated. When the entire statement
runs won't it strip that zero (0) '
Scuba79
"Hugo Kornelis" wrote:

> On Wed, 26 Jan 2005 08:57:01 -0800, scuba79 wrote:
>
> Hi scuba79,
> If you take your original query and change each terminatingidddcitycode to
> REPLACE (terminatingidddcitycode, '-','0'), it should work.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Alejandro using the following:
CREATE TABLE [Table2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TerminatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[OriginatingCountryCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TerminatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[OriginatingIDDDCityCode] [varchar] (8) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000002','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0052', '0000', '00000-68','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '007', '0000', '00000003','00000000')
INSERT INTO Table2 (TerminatingCountryCode, OriginatingCountryCode,
TerminatingIDDDCityCode, OriginatingIDDDCityCode)
VALUES( '0079', '0000', '00000-79','00000000')
The results that I looking for are when you combine fields
TerminatingCountryCode and TerminatingIDDDCityCode should be
522
52068
73
79079
Hope this helps and thank you for the assistance
Scuba79
"Alejandro Mesa" wrote:
> Can you provide some DDL (for those columns), sample data and expected res
ult?
>
> AMB
> "scuba79" wrote:
>

adding All option to a sorted list

Hi.
My DB holds a table with a varchar column containg numbers.
In RS I need to create a dataset that will sort these numbers and add 'All'
value to this dataset.
My problems is:
As the dataset holds sting values and it performs lexicographic sort, if I
perform cast to int on these numbers (to sort them correctly) I can't add the
'All' value.
Furthermore, Does anyone knows how to refer to a custom made dataset defined
in the RS?
Thanks,
GuyTry something like
select right('0000' + myvarcharfield,4) from table order by 1
union
select 'All'
for a set of numbers not longer than 4 characters... This will sort them
correctly I think without converting them to ints
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"GuyR" <GuyR@.discussions.microsoft.com> wrote in message
news:56FE3261-1DEF-48DD-B4B3-07F2F992AC4A@.microsoft.com...
> Hi.
> My DB holds a table with a varchar column containg numbers.
> In RS I need to create a dataset that will sort these numbers and add
> 'All'
> value to this dataset.
> My problems is:
> As the dataset holds sting values and it performs lexicographic sort, if I
> perform cast to int on these numbers (to sort them correctly) I can't add
> the
> 'All' value.
> Furthermore, Does anyone knows how to refer to a custom made dataset
> defined
> in the RS?
> Thanks,
> Guy
>