Including Newline In SQL SELECT Statement
Nov 21, 2006
Hi,
I'm trying to write a SQL SELECT statement where the phone numer ("telnr") is divided on three rows. How do I write a newline? I've tried
, NEWLINE, and a few others.
Thanks in advance!
Pettrer, Sweden (VB, Sql Server, VWD Express, Asp.Net 2.0)
Code:
SelectCommand="SELECT [gID], [enamn], [fnamn], telnr1 + ' ' + telnr2 + ' ' + telnr3 As telnr, [epost] FROM...
The corresponding gridview's cell's value is
08-43 244 234 08-432
23 08-424333
and should be
08-43 244 234
08-432 23
08-424333
View 7 Replies
ADVERTISEMENT
Apr 21, 1999
Hi All
I have a table of students, and a table of lecturers, and I wish to use one select statement to return a list of users.
What I really need to do is something like this:
SELECT Students.UserID AS UserID, Lecturers.UserID AS UserID, Students.FullName AS FullName, Lecturers.FullName AS FullName FROM Students, Lecturers
which would (in my dream world!) produce something like:
UserID FullName
s0002131 Darren Student
s0054242 Richard Student
e13412 Michael Lecturer
x92342 Linda Lecturer
Does anyone know how I should do this? Should I be doing it in an stored proceedure and returning the record set from that? Currently I'm only using ASP and one line SQL statements, and would like to continue doing so.
View 1 Replies
View Related
Aug 12, 2012
An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
tblErrors_ER
---------------
CREATE TABLE tblErrors_ER
{
ErrorID int,
ErrorType varchar(255),
DateLogged datetime,
[code]...
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 Multiple
2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
View 2 Replies
View Related
May 24, 2015
SQL express 2012. I am trying to case in the where part and having a syntax errors - This is what i am trying to do:
select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime
[Code] ....
View 2 Replies
View Related
May 24, 2015
SQL express 2012
I am trying to case in the where part and having a syntax errors - this is what i am trying to do:
Select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime
[Code] ...
View 2 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jul 20, 2005
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View 2 Replies
View Related
Oct 29, 2007
Hi guys,
I have the query below (running okay):
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:
Field02
----------------------
22810
8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760
22810
Is there any good suggestion?
Thanks in advance for any help,
Aldo.
View 3 Replies
View Related
Feb 26, 2008
Hi everyone!I'm be stuck by a simple issue about newline in C# and has relative with SQL. I wrote a SQL sentence like that: IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME = 'TG1' AND TYPE = 'TR')" + " DROP TRIGGER TG1" + " GO " + " CREATE TRIGGER TG1 ON FILES FOR DELETE AS DELETE table1 FROM" + " table1 INNER JOIN DELETED ON DELETED.ID = table1.ID" + " GO " + " DELETE FROM table2 WHERE ID = 'ID00001'It's executing ok in SQL but when execute programming then " GO " have to break newline (
or System.Environment.NewLine), However seem have not mean for SQL Execute becoz will be appear
in SQL sentence. I'm not have experience about GO and TRIGGER so have any idea for this problem! Thanks so much.
View 2 Replies
View Related
Jun 25, 2007
Hey guys, how do I return a string from a stored proc that displays some of its text on a newline?
E.g.
"some text goes here
some more text goes here
etc etc etc"
View 4 Replies
View Related
Jul 4, 2006
Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E
can any one help me in this?
please give me a sample query.
Thanks and Regards,
Kiran Suthar
View 7 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Apr 9, 2007
I have a SQL 2005 stored procedure to generate an email when passed parameters such as receipient, subject etc
One of the paramteres passed to it is @body which is the body text of the message. I want to be able to add a couple of blank lines and then some footer information. This is working right now except I can't find the right way to add newlines into the string within the store procedure, so my footer information just tags right on after the bodytext.
I have tried but that literally adds the two characters and n
Can anyone advise how to generate newlien sequences in T-SQL.
Regards
Clive
View 1 Replies
View Related
May 10, 2005
Hello,
I have data in a database table that has newline data in it. It has a newline character in front of the text of the data, and for some reason, I can't figure out how I can replace it. I've tried entering in a return character between '' and I've tried trimming the text... How do I represent a newline character to remove it from the text?
Thanks.
View 1 Replies
View Related
Sep 21, 2006
hi all..
i have a text field which has to be shown in the result of a query execution as several lines..
eg: the result should be like this
8STK :: L/2
: M/2
: S/1
Original string is stored as "8STK :: L/2 : M/2 : S/1"
expect a positive reply soon..
thanx..
View 6 Replies
View Related
Jun 1, 2004
what is the newline character in sqlserver.
My problem is ,I have to display text (say "Hi abcd hhh") where is the newline.
But when i print this in sqlserver storedproc,Its displaying as it is instead of newlines.
please help
thx
View 2 Replies
View Related
Feb 28, 2002
Can I use a newline character in my query? I want to dynamically generate a statement, split on several lines. Simple example:
declare @cmd varchar(100)
select @cmd = 'select user_name(), getdate()'
select @cmd
The output will be: select user_name(), getdate()
Can I use something like this:
declare @cmd varchar(100)
select @cmd = 'select user_name(), getdate()'
select @cmd
so as to get an output like this:
select user_name(),
getdate()
Thanks.
View 1 Replies
View Related
Apr 25, 2005
Hi i get the above error when moving from one page to another. the following code is used. Can u help
Response.Redirect("Applicationform2.aspx?Id="+StudentID.Text+"&nam="+Name.Text+"&surNa="+ SrName.Text +"&DOB="+Ddate+ "&addr="+address.Text+"&pCode="+postal.Text+"&Cntry="+country.Text+"&email="+email.Text+"&ph="+phone.Text);
i dont understand the problem, can u help resolve the problem
View 1 Replies
View Related
Apr 20, 2006
I am using Derived Column Transformation Editor. I have 3 string values which I would like to combine them into one string and have a newline character inbetween the 3 strings. I cannot see a Char() function similar to TSQL to use for this purpose. I thought about creating a Variable but even to that I don't know how I can assign a newline character.
Any ideas?
View 5 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Aug 10, 2006
I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View 1 Replies
View Related
Sep 17, 2007
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View 2 Replies
View Related
Nov 7, 2005
Hi, I want to add a newline in a content of sql column (using t-sql and not asp.net textbox) so when content is being rendered in a .net textbox I get separate rows, so insteadcol1 col2 col3 I woule like to havecol1col2col3Thanks
View 3 Replies
View Related
Mar 9, 2000
I exporting a table of comments. There are some line returns in the comments. Some of these data are paragraphs of data! For some reason, when I am exporting the data, it treats the line return within the comment column as a new record. I am using a -c character data type so (newline character) is the row terminator. How do I get the BCP OUT to ignore a newline character within a record?
For example:
ID~Comment
-- -------
1~This is a comment
2~Hi,how are (user hit carriage)
you (you is part of next row in bcp out)
3~Next record
Thanks!
Joyce
View 1 Replies
View Related
Oct 8, 2004
while(@@FETCH_STATUS=0)
begin
print 'inside while'
set @BodyContents = @BodyContents+@gotclientName+@gotproductName
set @Body= @Body + @BodyContents + ','
fetch next from resultcursor into @gotclientName,@gotproductName
print 'going outside of while'
end --while end
Client Name Product Name
aaa test1
bbb test4
how to format row like above. currently i am getting
aaa,test1,bbb,test4
View 1 Replies
View Related
Feb 5, 2015
I need to import a CSV file to a table and the CSV has an Address field that has a carriage return in it.
Example:
123 Main St.
Anywhere, CO, 99999
I'm working in Windows with SQL Server 2008. What can I do to the CSV file or from within SQL Managment Studio to get the BULK INSERT to work?
Here's my query:
BULK INSERT Contact
From 'C:UsersBrianDownloadsImport-FilteredContact9c.csv'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '
'
)
View 1 Replies
View Related
Aug 6, 2007
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
View 2 Replies
View Related
Jul 11, 2007
Hello
How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement.
<% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>"
ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)">
<SelectParameters>
<asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" />
</SelectParameters>
</asp:SqlDataSource>any help would be appreciated
View 2 Replies
View Related
May 15, 2008
Hi,
I am a newbie to this site and hope someone can help....
I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is:
if(TL_flag= '1', "yes") as [Trial Leave]
it is coming up with an error.... I can use Select case but I should not need to as this should work?
Any ideas?
View 2 Replies
View Related
Jun 14, 2007
I have the misfortune of converting a DTS package to SSIS that loads a flat file that has a text fields that can contain embedded text delimiters ("), column delimiters (,) and even new lines (CR+LF i.e.,hex 0D 0A) in it. A sample line from the file is posted here, remember this is just one line though it shows as three lines, since the third field has embedded new line in it:
4,"Sam","EVP; MARKETING PRODUCT MANAGER ""Level I"",
Internet Sales / HELP
8005551212",100
If you open in excel it handles it perfectly showing four fields, as below, and this is what I want ( I cannot get it aligned right in the posting, just save the above line in *.csv and open to see what it should be):
4 Sam "EVP; MARKETING PRODUCT MANAGER ""Level I"", 100
Internet Sales / HELP
8005551212"
Now, SSIS errors on the embedded text delimiters and breaks into two or three lines based on which option I chose. I have tried few options based on postings in the forum:
a). Using undouble and undoubleout: Does not work when there are embedded column delimiters (,) in the text field
b). Modified undouble script posted by lvovg at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1718225&SiteID=1. Handles the embedded column delimiters (,) perfectly, but the embedded new lines (CR+LF i.e.,hex 0D 0A) are breaking it.
Since, I am using the ragged right format to read from the file then use transform script on the line by lvovg, the line is already broken by the ragged right format at the embedded new lines, hence does not work.
Right now I am stuck. Can someone please help (anyone from MS) ? I am already baffled at the amount of coding required to convert a very basic ( and working ) flat file load DTS package to SSIS. I am willing to persist bit longer to convert this to SSIS, before I give up and stick with DTS and wait for a fix / workaround.
Thanks.
View 7 Replies
View Related
Sep 28, 2007
Hi,
I am bulk inserting the data from a file into a table using the following format.
9.0
1
1 SQLCHAR 0 10 "
" 1 MSISDN SQL_Latin1_General_CP1_CI_AS
This works perfectly for me.
But my problem is that the firmat may not be generated in Windows and i can't user "
" as row delimeter.
It could be generated in Unix and want to use LF as the row delimeter.
Questions :
1) How do i specify this kind of new line (LF ) in formatfile?
2) Is there anyway that i can specify both the delimeters and let it use which ever is appropriate ?
Please provide some inputs on this??
Thank u so much
~Mohan
View 3 Replies
View Related
