Hi,
I have a table with a "description" row.
In this row I may have some simple quote (') character.
How can I select them ?
something like :
select * from myTable where description like %'%
thanks
I am trying to find all instances of a string that contain the letters FSC. While I am able to find most of them, I am unable to find the ones wrapped in double quotes.
Query example:
Select *
from myTable
Where item like '%FSC%'
This works great with the exception of when FSC is surrounded by double quotes.
ex: MySentencecontains"FSC"
I have tried Replace with no luck.
Hi All,
I'm having trouble importing a CSV file into SQL using SSIS. The trouble seems to stem from truncation and from quote encapsulated fields.
Firstly it's worth noting that some of the data within the quotes contains the separator. E.g.
12,"some text, and a comma",34
Thankfully SSIS seems to cope with that by specifying " as the Text Qualifier. My next problem was that the SSIS Import and Export Wizard gave an error: "failed because truncation occurred". But I fixed this by specifying the OutputColumnWidth for the NCHAR and NVARCHAR type columns to be the width from the table definition.
But now I have another problem with the length of fields. Consider the following where the center column is NCHAR (22)
101,"some text, and a comma",303
102,"some ""quoted text"" bye",303
The first row has the correct width (once the encapsulating quotes are stripped out), but the second row does not, because it seems that when I exported the table the export wizard escaped the quote characters within encapsulated strings using quote characters. So I am back to getting the "truncation occurred" error. How can I get around this within SSIS?
Cheers,
Tim.
Help. Several people have told me to connect to SSIS from SSMS, but I don't know how. Can someone PLEASE tell me how to connect to SQL Server Integration Services from Management Studio?
Thank you
Does anyone know where to find a simple sample showing you how to use asp.net 2.0 with the login control to direct you to another aspx page using SQL 2000 as the database on a hosted server? I have been beating my head against the wall trying to figure this out and would be very appreciative if anyone knows a place out there that shows this? I can find many sites that show you how to do this with SQL 2005, but not SQL 2000. Thanks - Chris
View 4 Replies View RelatedReplace the <<job name>> with the actual job name
---Last n RUN status of the job------
SELECT TOP 20
SJ.name 'JOB Name'
,'Run date : ' +
REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,run_date)),102),'.','-')+' '+
SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_time),6),5,2) 'Start Date Time'
,SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),1,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),3,2)+':'+SUBSTRING(RIGHT('000000'+CONVERT(varchar,run_duration),6),5,2) 'Duration'
,CASE run_status WHEN 1 THEN '1-SUCCESS' WHEN 0 THEN '0-FAILED' ELSE CONVERT(varchar,run_status) END AS 'Status'
,Step_id
,[Message]
,[Server]
FROM MSDB..SysJobHistory SJH
RIGHT JOIN MSDB..SysJobs SJ
ON SJ.Job_Id = SJH.job_id
WHERE SJ.name LIKE '%<<job name>>%'
AND Step_ID = 0 --Comments this line if you want to see the status of each step of the job
ORDER BY run_date DESC, run_time DESC, step_ID DESC
---Last n RUN status of the job---------
Getting following error when I run this query. How do i handle apostrophe?
update holidays
set name = 'May 13 - Mother's Day',
month = 5,
month_day = 13,
holi_type = 1
where id = 52
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark before the character string ',
month = 5,
month_day = 13,
holi_type = 1
where id = 52
'.
I ran a large query and exported it to xls. In Excel, I noticed a value of '2.00E+01'. I formatted the Excel cell and then got a value of '20'. I searched for the record in SQL Server 2008, ya I know, ancient. The value is '2E1' which excel is seeing as scientific notation.
Can I use a case statement that when an 'E' is present to add a single prefix quote like '2E1? Or perhaps do it for the whole column? Or put put double quotes around the whole field?
I have a stored proc that creates a view so I can pass parameters. I need to replace the 120 with the variable @MEA. I can't get my quotes right to make this work.
ALTER PROCEDURE dbo.spi_CallList
(@strAgent nchar(4), @MEA int)
AS
DECLARE @Msg varchar(255)
declare @SQL varchar(4000)
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList1')
DROP VIEW qs_CallList1
SET @SQL = 'Create view qs_CallList1 as
SELECT top 100 percent tsd_Claim.clinsnum AS [Ins#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByIns
FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim)
AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat))
LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID
WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''')
AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null)
AND ((tsd_Claim.clfromdos)<GetDate()-120))
GROUP BY tsd_Claim.clinsnum
ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC'
EXECUTE(@sql)
How do I insert a single quote ' into a table? For example:Insert mytable values (1,''')I get an error message.Any ideas? The server does not recognize double quote (why? is thistsql, ansi?), otherwise I could have written it as:Insert mytable values (1,"'")Thanks.
View 4 Replies View RelatedI am using SQL Server 2005, I have a NVarChar parameter called @Text that I use to build a dynamic Where clause for my SELECT Statement.
SET @l_Where = @l_Where + N' AND (StatusDesc LIKE ''' + Replace(@Text, "'", "''") + N'%'')'
This gives me an invalid column error.
How do I replace the single quotes in @Text with 2 Single Quotes?
TIA,Jason
I have 2 SQL 6.5 databases on separate servers. Server A replicates a text field into a table on server B.
On server A the field contains text similar to THIS IS FRED'S HOUSE. After replication to Server B it looks like THIS IS FRED''S HOUSE. The distribution database also has it as THIS IS FRED''S HOUSE. Using ODBC trace I cannot see the value being passed in the text field as it is displayed as a question mark e.g. ?.
How do I fix the problem ?
Thanks
How do I update a record with a string containing a single quote ' ???
SET Row=' charlies' dilemma ' doesn't work since the string gets cut in half
Hi everyone -
Here is the setup -
new install, same old software
only difference is the machine name has
a single quote (apostrophy) in the name...
xyz's
Now - a portion of the application is failing -
the starting of the application runs ok,
and the system is indeed seeing the database
(it allowed the user to log in)
so the connection string seems to be ok - just in that
one part...
does anyone know if the machine name with an embedded
apostrophy will cause the ADO connection string to puke???
vb6
MSDE
thanks
tony
Hi,
I am trying to concatenate single quotation mark (ie- ' ) in the select query but not succeed.
If I concatenate word India's, how should I have to write the select query..?
Its just query I have not concern with any specific database.
Hi,
i am trying to add a single quote to a string. This is a must because i am making a full select statement in which i need the single quote to compare values. Obviously this breaks my string invalidating my query.
ej:
SELECT avg(tabla.ip_trend_value) as valor, FLOOR(Cast(tabla.ip_trend_time AS FLOAT)) as tiempo
FROM TESTLAB5.dbo.CE02_L21_916AI31_43 tabla, TESTLAB5.dbo.CE02_L21_916XI31_4 t2
WHERE t2.ip_trend_value = 'Alimentacion Digestores' and t2.ip_trend_time = tabla.ip_trend_time
group by FLOOR(Cast(tabla.ip_trend_time AS FLOAT))
and this will become something like this.
SELECT @TableName = 'TESTLAB5.dbo.'+@TableName
SELECT @SQL = 'SELECT avg(tabla.ip_trend_value), FLOOR(Cast(tabla.ip_trend_time AS FLOAT)) FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' tabla, TESTLAB5.dbo.CE02_L21_916XI31_4 t2'
SELECT @SQL = @SQL + ' WHERE t2.ip_trend_value = '@NombreVar'and t2.ip_trend_time = tabla.ip_trend_time'
SELECT @SQL = @SQL + ' group by FLOOR(Cast(ip_trend_time AS FLOAT))'
the @NombreVar is the equivalence of 'Alimentacion Digestores'.
is there something i can add or change to make it work ?
Hi Guys,
I have a problem with one of my apps. The apps was written long long time ago in C++ and uses SQL server 7
The problem is that when the users key in some data with single quote in it the query failed, obviously. Is there any way that SQL Server automaticaly manages this kind of Insert/Update statements ?
Thanks & Best Regards,
Faisal.
I'm inserting a row of people's names and addresses to a table. This seems to work great, unless the person has ' in their name, for example O' Riordan. When the address has single quote (such as Wilder's Path), I'd get the same error, unclosed quote when inserting the row.
What would be the best way to work around this, other than read the line and replace it with a space.
Hi
I have a table with a few hundred emailadresses. How can I delete all quotes (') from the addresses, so that 'email@email.com' is replaced as email@email.com.
Thank you
zipfeli
HI,I anm geting error when i want to store some text which contens single quote like this Hi I am 'santosh'.as i am using text editor which genetates XML data (not pure) so i have used varchar(max) to store the data but it gives error.
Is thier any way to store text with single quote........
urgent plz.
Hi,
I am beginer to ASP.NET. I want to insert ' ( single quote ) which is entered by the user in a textbox of the ASP.NET web page. As you all know in the insert command the column is ending at that single quote.
In my asp.net page when I run a query against the database the datagrid get populated with only the records that starts with the first letter for example A good day. But none of the records that starts like this "A fine day" or "A nice day" displays in the datagrid. I tried to replace the Double Quote but it still returns only the records without " quotes. My query looks like this:
Dim queryString As String = "SELECT [Articles].[AN], [Articles].[Department], [Articles].[ArticleHeading], [Articles].[AccessLevel], [Articles].[Status] FROM [Articles] WHERE (([Articles].[AccessLevel] <> 'SysAdmin') AND ([Articles].[Status] = 'Enable') AND (REPLACE([Articles].[ArticleHeading], 'chr(34)', '')) like @ArticleHeading) ORDER BY [Articles].[ArticleHeading]"
I WANT TO INSERT A SINGLE QUOTE IN TEXT AS "ABC'S"
INSERT INTO TABLE1 (CODE,NAME) VALUES ('A001','ABC'S')
Some of my records starts with a double quote, when I do a search for all records starting with A I can get all except the ones that start "A fine day ...
View 4 Replies View RelatedHi all
How to enter single quote character in a sting column for eg: Channel's
Thanks
How do I get a single quote (') in a NVARCHAR string in MS SQL Server?e.g. SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah''Obviously this is invalid as the single quote before "blah" would end thevarchar string.How do I get round this?
View 8 Replies View RelatedHi
Declare @Customer varchar(255)
Set @Customer = Single quotes + customer name + single quotes
Select Customerid from Customer Where name = @Customer
I have a query written above, but i was not able to add single quotes to the set statement above. Can i know as how to go about it?
Early reply is much appreciated.
Thanks!
i got this error when i was trying to deploy the report... in the preview mode report is working good....
D:
eport1.rdl The expression for the query €˜dataset€™ contains an error: [BC30648] String constants must end with a double quote.
can any one gimme the solution
I'm cleaning up a column in my table and getting rid of special characters.The only think I can't get rid of with the REPLACE function is single quotes.I'm doing aUPDATE TableSET Column = REPLACE(Column,'''','') --that's four single quotes then two single quotesBut the single quotes in my column wouldn't go away.I know that
DECLARE @string varchar(50) = 'test''s strings'SET @string = REPLACE(@string,'''','')SELECT @string
Hi There,
I can't figure out what is causing this error: can you please have a look at my code and let em know what is wrong or rework it for me:
="SELECT ARIBC.cntbtch, ARIBC.btchdesc, ARIBC.AUDTUSER, ARIBC.AUDTDATE, ARIBC.AUDTTI, GLPSOFTMAP.PSPRODUCT, GLPSOFTMAP.PSDEPT, GLPSOFTMAP.PSACCOUNT, GLPSOFTMAP.ACCTID, ARIBH.CODECURN, ARIBH.INVCDESC, CONVERT(varchar,ARIBH.FISCYR + ARIBH.FISCPER) as PERIOD, CONVERT(varchar,ARIBH.IDCUST) AS ACCOUNT, ARCUS.NAMECUST as ARNAME, CASE TEXTTRX WHEN 1 THEN 'INV' WHEN 2 THEN 'DM' WHEN 3 THEN 'CM' END AS CLASS, CONVERT(varchar,ARIBH.IDINVC) AS TRX_NUMBER, (CASE TEXTTRX WHEN 3 THEN ROUND((ARIBD.AMTEXTN*-1),2) ELSE ROUND((ARIBD.AMTEXTN),2) END) AS ""AMOUNT FROM ARIBC"" INNER JOIN ARIBH ON ARIBC.CNTBTCH = ARIBH.CNTBTCH INNER JOIN ARCUS ON ARIBH.IDCUST = ARCUS.IDCUST INNER JOIN ARIBD ON ARIBH.CNTBTCH = ARIBD.CNTBTCH AND ARIBH.CNTITEM = ARIBD.CNTITEM INNER JOIN GLAMF ON ARIBD.IDACCTREV = GLAMF.ACCTFMTTD INNER JOIN GLPSOFTMAP ON ARIBD.IDACCTREV = GLPSOFTMAP.ACCTID WHERE ARIBC.BTCHSTTS=3 AND ERRENTRY <= 0 AND BTCHDESC NOT LIKE '%54-8003%' AND ARIBH.IDINVC in ('" &Replace(Parameters!invoicenum.Value,",","','")"
Thanks,
RC
found it
View 3 Replies View Relatedhi,
how to avoid single quote character in string which comes from textbox in insert query
Hi ,
I'm using sql server 2005. In my 'Books' table some of the 'subject' column contains data's with single and double quote. example: Quantu"m phys'ics
i'm passing the subject as parameter and it have to display results if the corresponding subject name is in the table. Here is my code... i'm using dynamic query...
alter procedure getbooks
@sub varchar(200)
as
begin
declare @sqlq nvarchar(2000)
set @sqlq='select subject from books where subjectname like '''+@sub+'%'''
exec sp_executesql @sqlq
end
How can i search for words like this Quantu"m phys'ics in the above query? Any one who knows how to do this please send me the code..