Is it possible to change any line breaks or newlines for a string ...
In a select statement in a store procedure, in this way I can reproduce text in a label on a webpage complete with line breaks ....
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
I wanted to know how to insert line breaks while updating the database. Even if I have a number of paragraphs......everything is displayed as one single paragraph. How do i display text in paragraphs ?
Hope someone can help me out. Thanks in advance.
I am fairly new to SQL Server. I work for an Internet sofware company building web sites for our customers.
I am trying to insert JavaScript ad tags into a text field in my db. It seems that when the calling web page is rendered the line breaks are removed from the js code and it does not function properly. I've doubled up all of the single quotes and the line breaks appear in the insert statement but do not make it to the front side of my web site.
I have a field in a db table which is like a chat history.I need to update the field with a user's input (put into a multiline text box) and append that to the beginning of the field.The problem is that I want to maintain line breaks when I retrieve the field into a multiline textbox (aka textarea) and can't figure it out.I've searched around and have seen lots of solutions for converting line breaks into <br> tags, but those people are trying to output to a label.I'm using an update command in my SqlDataSource like this: UpdateCommand="UPDATE [account] SET [history] = @history + history WHERE [id] = 1"With a parameter declaration like this: <UpdateParameters> <asp:ControlParameter Name="history" ControlID="TextBox1" PropertyName="Text" /> </UpdateParameters>If I try to concatenate in a or CHR(10), the characters are literally inserted. Like this for example (this is the last variation I tried).... UpdateCommand="UPDATE [account] SET [history] = @history + CHR(10) + history WHERE [id] = 1"How do I store a CrLf programatically?
View 6 Replies View Related
I have e-mails saved in a SQL database with all HTML formatting information.
To remove all HTML formatting I craeted a function on the RS report:
Public Shared Function removeHtmlChar(ss)
try
Dim l as integer
Dim i as integer
Dim ch as string
Dim x as integer
Dim t as string
t = ""
l=len(ss)
for i=1 to l
ch=mid(ss,i,1)
x=i
if(ch="<") then
x=i
do while(mid(ss,x,1)<>">")
x=x+1
loop
else
t=t & ch
end if
i=x
next
t = t.Replace("nbsp;"," ")
removeHtmlChar = t
catch
removeHtmlChar = ""
end try
End Function
When I look at the report on the screen the report is showed correct.
If I print or export the report it add a lot of line breaks in the e-mail text.
Why does it not print it as it looks at the screen?
Hello,
I have a series of fixed width files, all with the same schema. I need to import the data into a SQL Server table. Each record in the flat file begins with 'D1'. The length of each record (string) is 380. There are cases where the record ends after position 193, and a new record appears in the current string beginning at position 194. So at position 194 'D' appears, and '1' appears at position 195.
In the flat file, I need to insert a line break after position 193 if position 194 = 'D' and if position 195 = '1'. I'm guessing I would do this with a Script Component Transformation. Once the file is edited, then I can bring the data into the table.
What might the script look like? If you have any suggestions, samples, or know of examples on the web you can point me to, please share.
Thank you for your help!
cdun2
Is there something I can do in layout mode to cause line breaks to only occur when data changes? I am creating a list of students and their degrees, but some students have multiple degrees and some only have one. If I place the information in a table with a blank row for a break, it breaks after every degree, but without the break there are no breaks at all.
Here's what I want:
Student
degree
degree
Student
degree
Student
degree
degree
degree
But what I have is:
Student
degree
degree
Student
degree
Student
degree
degree
degree
Is there any way to fix this, perhaps with an expression of some kind?
Hello,
Since SQL 2005 Express doesn't appear to let you paste multiple lines of data into a cell -- a task as "basic" as the Commodore 64 -- I'm forced to enter the data for 2 records directly. I'm trying to insert line breaks, which SQL 2000 allowed you to do by simply pressing the Enter key. However, 2005 just moves you to another record. How do I do this extremely simple task?
Many thanks for a speedy reply!
I can't figure out how to get my line chart to break when there isn't a value. For example, I have a trend line over 4 time periods. The 3rd time period is missing a value. Instead of the line ending at the 2nd period and picking up again at the 4th time period, it's connecting the line 2nd to the 4th period. I'd like it to break and for there to be no line appearing in the 3rd period. I bet that's as clear as mud, but let me know if you have any questions.
Thanks!
sash
I am storing formatted data (including spaces and line breaks) in a single field in a table.
When I run a report on that field, the preview of the report is automatically removing all the extra spaces and line breaks, making the report unreadable.
When exporting the report to PDF or printing it, it shows the line breaks and spaces as expected.
Does anyone know how to make the report preview show the spaces and line breaks?
Hi All,
I have a simple SSIS package that runs a query on the db and outputs a fixed width flat file. I have all my column widths defined and in the connection manager i can preview the output. Everything looks great. All the fields fall where they should and each record is on it's own line.
When i run the SSIS program and then go open my text file with a text editor the ouput is all on the same line. I have tried changing my file format from fixed width to ragging right and adding a row delimiter but that doesn't work either. I feel like i'm missing something small here. It could even be an issue w/ my text editor (although i've tried to open the text file in multiple editors). In the flat file connection manager I have my file defined to be 187 characters long, So figure every 187 characters it should output a new line (it should add the carraige return right?).
Has anyone encountered an issue like this?
Any help would be much appreciated.
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
How do I execute below scripts using CMD:
Please Note: SQL server 2008r2, default instance with window Authentication.
1. Select * from Adventurewoks2008.Person.Person where Id = 5
2. Exec usp_getemployee (with parameter Manager_id = 100)
3. I have a folder named 'MyFolder' which contains 3 store procedures (usp_A, usp_B and usp_C).
How do I execute all 3 using CMD against AdventureWorks database.
Hi
I need to Install the SQL server 2008 express with Instance name and Sa password parameter in silent mode . I tried couple of command line arguments for this. but It didn't work, I got the setup failure error message
"The setting 'QB' specified is not recognized.Error code 0x84B40003."
Can any one help me with this?
These are the commands I tried.
1) sqlexpr.exe /qb instancename="MACHINENAMESQLEXPRESS2008" SCCCHECKLEVEL=IncompatibleComponents:1;MDAC25Version:0 ERRORREPORTING=1 SQLAUTOSTART=1 SAPWD="PWD" SECURITYMODE=SQL DISABLENETWORKPROTOCOLS=0"
2)SQLexpr.exe /qb /ACTION=Install /FEATURES=SQL /instancename="MACHINENAMESQLEXPRESS2008" SAPWD="PWD" SECURITYMODE=SQL
Thanks in advance.
Regards,
Nav.
I have 2 tables:
1 is with only one field: numerator
2ed is (for example) sales information.
I need to export (using SP) the sales information with unique numerator per line. So,
I need to get the value from table 1 (numerator),
I need to promote the numerator by 1 for every line, and
I need to update the first table (numerator) (as i need to keep it updated for the next run).
At the next run I need to get again the updated numerator from the first table, and so on...
Row Value :
Fiscal Year: 2016(
)Budget Scenario: Main Budget (0+12)(
)Forecast Scenario: Jun (0+12) Forecast(
)Department: 400 - New York(
)YTD Period: Jun
and I need to extract 400 only... substring fails because the length of the Scenario, Forecast etc differs.
Should I just go for charindex on break() or is there a another way ?
I'm in the process of importing a series of flat files into SQL Server. I'm using a ~ to separate the columns and the row delimiter is {CR}{LF}. One of the files has a field that contains the CRLF combination in a few places so that field is split over several rows. This is readily visible when I look at the flat file. However, when I'm importing the file, the Import and Export wizard seems to ignore them and import the files as they should with one row per record.
View 0 Replies View RelatedI have tables in database where a VARCHAR(50) string is unique identifier. The database currently has an integer identity column as clustered primary key, and there's a non-clustered index on string column. The customer always queries based on a defined set of the identifier (string) column.
I wonder if someone sees an advantage of adding a persisted computed column to the table as the checksum of the string column, and then create a non-clustered index on the checksum and the string. When a customer requests data, we would compute the checksum of the customer provided identifier and add to the where clause or join, that the checksum and string must match.
Will SQL Server perform checksum check (integer) and only if it succeeds, perform the string check, in which case I see an advantage of added the checksum column? Or will SQL Server always check for both the checksum and string, in which case the additional column only adds unnecessary overhead? To note is the fact that the table(s) will have millions of rows, but the customer will request data for at at most, 100 or so identifiers.
I need to compare to string in sql
requirement is
Select Reson_id from reason_data where reason='ryete / werewr ddsad '
is there any way without string function? bcz string function take more exaction time
Hi,
I need to store large note in SQL Server field, what is substitute of Access's Memo filed in SQL Server?
Appreciated...
Interest rate has been stored in comments column along with other information ( e.g. mike's student loan is 5% and car payment is $ 150). I need to extract 5% using Contains .. Why Contains? because it's a 1.7 m rows dataset and searching for fours specific interest rate values (e.g. 3%, 9%, 12% and 15%)
View 3 Replies View RelatedHere is a sample order # we used for one of our shipments: BL-53151-24954-1-0001-33934
I need to extract the "24954" portion of that order # while within an INNER JOIN, but not sure how.
My problem is we have 2 order tables: OrderTable1 contains a field with the full order #. OrderTable2 contains a field with only the "24954" portion. I need to JOIN on these 2 fields somehow.
So an example would be the following:
OrderTable1.Full_Order_No: BL-53151-24954-1-0001-33934
OrderTable2.Order_No: 24954
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM
OrderTable1 ot1
INNER JOIN OrderTable2 ot2 ON ot2.Order_No = [do something here to truncate ot1.Full_Order_No]
How can I do this?
Few notes:
-the 1st part of the order number, "BL-53151-" will ALWAYS be the same. It's our client # which will never change for the purpose of this query.
-The portion I need (24954) can be more or less than the 5 current digits.
-There will always be 6 portions to the order number, split up between 5 dashes.
I've below value in a column with data type - TEXT
QU 221025U2V/AN G-DT DL A 5 1A- 11,5,SF,230,30162,LZ,2,118,0,0,10170,25,06
This text value has some special characters in it. and I could not paste the exact value as this text box is not allowing me to do so. So, for reference I've attached a screenshot (Capture.png) of the value.
I want to fetch last two values from this text i.e. 25 and 06. (It can be anything like 56R,06T but will be the last two values separated by comma)...
How can we identify the Date Format from a String in SQL Server.
I might get an input from external source as "MM-DD-YYYY" or "DD-MM-YYYY" or "YYYY-MM-DD" or "YYYY-DD-MM", all i have to do is return the same with Current Date in the same format as Input.
Ex :
1. "02-20-2013" -> "05-28-2015"
2. "2014-04-19" -> "2015-05-28"
Any method to identify the DateFormat then it would have made the job very easy.
Anyny in-built sql function that gives us numeric values in a string?
I have to deal with some inconsistent US phone numbers stored in DB. They are stored as
(xxx)xxx-xxxx
xxx xxxx xxxx
(xxx) xxx-xxxx
xxx-xxx-xxxx
xxxxxxxxxx
I don't want to apply nested REPLACE function to eliminate all unnecessary characters to get 10 digit number from DB.
DECLARE @search VARCHAR(10);
SET @search = 'dog,COW';
WITH T(C)
AS
(
SELECT 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
UNION ALL
SELECT 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
)
SELECT *
FROM T
WHERE C LIKE '%' + @search + '%'
I have a string in a cell 'Cat,Dog,Sparrow,Trout,Cow,Seahorse'
I want to search any of the two names from the string.
Search string:'cat,cow'
Result:no result (not working),[size="7"][/size]
Search string:'cat,dog'
result :given string(working fine )
Why can i get the numeric values from this string?
{_cpn}=1743; {_cpnll}=4511
Result: 1743, 4511
Position and len of the value can be different...
We're converting to new student info system. Sometimes registrar entered the same school into the schools table but spelled it differently. Trying to find all student assigned transfer credits from the same school but the school name is different. My db shows a max of 9 different schools students have rec'd transfer credits. Spending too much time trying to figure out best way to do it w/o a ton of IF stmts. Looking at Soundex and Difference functions. Still looks like a lot of coding. how to compare up to 9 string variables in sqlserver 2008?
View 2 Replies View RelatedI have a SQL select syntax as below
0 AS SalaryMin,
2088 AS SalaryMax,
2088 AS BillableHours,
'Month' AS SalaryPaidCode,
0 AS SalaryBreakdownHourly,
0 AS SalaryBreakdownDaily,
[Code] ...
While outputting to CSV.file
I got :0,2088,2088,"Month",0,0,0,0,0,0,0,"N/A","N/A","G","N/A","Exempt","Other",1
How can I remove all double quotes in the string fields? so that O can get the result as below while the output
0,2088,2088,Month,0,0,0,0,0,0,0,N/A,N/A,G,N/A,Exempt,Other,1
I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.
This is for a data cleanup.
CREATE TABLE #Names ( FIRSTNAME VARCHAR (20), LASTNAME VARCHAR (20))
INSERT INTO #Names VALUES
('Thomas', 'Hohner')
,('Mike', 'SSmith')
,('TtTony', 'Hawk')
,('Jeff', 'Smith')
,('ZZSara', 'Jones')
,('Luke', 'HendersonZZ')
,('Lily', 'PPadZZZ')
SELECT * FROM #Names
DROP TABLE #Names
We have a legacy database that have hundreds of stored procedures.
The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.
I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.
I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?
Our front end saves all IP addresses used by a customer as a comma separated string, we need to analyse these to check for blocked IPs which are all stored in another table.
A LIKE statement comparing each string with the 100 or so excluded IPs will be very expensive so I'm thinking it would be less so to split out the comma separated values into tables.
The problem we have is that we never know how many IPs could be stored against a customer, so I'm guessing a function would be the way forward but this is the point I get stuck.
I can remove the 1st IP address into a new column and produce the new list ready for the next removal, also as part of this we would need to create new columns on the fly depending on how many IPs are in the column.
This needs to be repeated for each row
SELECT IP_List
, LEFT(IP_List, CHARINDEX(',', IP_List) - 1) AS IP_1
, REPLACE(IP_List, LEFT(IP_List, CHARINDEX(',', IP_List) +0), '') AS NewIPList1
FROM IpExclusionTest
Results:
IP_List
109.224.216.4,146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119
IP_1
109.224.216.4
NewIPList1
146.90.13.69,146.90.85.79,46.208.122.50,80.189.100.119