Data With Semicolon Delimiter
Apr 18, 2008
Hi all......
I have an issue
There are 2 tables... source and target...
Data from source goes into target table under same field...
ISSUE IS -
Data from 'n' no. of records from source table goes into a single record in the target table with delimiter being a semi colon (;)...
where(n >=2)...
For example - if the source table has 'src1', 'src2' and 'src3' as the data then target table will have a single record with semicolon as delimiter as 'src1;src2;src3'
How do we compare the data under this particular field now...
Do we have to use a if then loop for identifying when the ; ends in target data...
kindly help by giving a example...
Thanks,
Avi.
View 1 Replies
ADVERTISEMENT
Dec 5, 2013
I have 8 fields - I have requirement to concatenate using '+' operator with semicolon delimiter but issues is in the
Output I get semicolons for the fields that are empty below is my code :
-------------
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [New Extension or Replacement Audit Flag] like 'FLAG%'
then 'Reject – New, Extension, Replacement invalid entry' --'it is jp'
else ''
end as [ES Fully approved data 1],
case
[Code] ....
View 6 Replies
View Related
Sep 27, 2005
I'm working with an existing program that crafts an insert statement
from user input. The data is machine generated, though, and some
of it has semicolons. How can I "escape" those semicolons so that
I can insert them into the database? Does the ESCAPE key work
with the insert statement, too? Can I just replace the ";" with
some other character(s) to escape it? I'm not finding much in the
online help. Thanks!
View 5 Replies
View Related
Jul 10, 2015
Currently I'm working on a few SSRS reports which will be exported to CSV.
In some of the fields e.g. "Description" the data can contain carriage return and line feed characters (CR LF). SO when that happens the csv export breaks the line and rest of the data from that fields goes into the net line. This creates a few undesirable line breaks.
I can always replace these characters from that fields.
Is there a way I keep those characters in the field and still my csv export come up fine.
View 4 Replies
View Related
Apr 27, 2005
Code:
<%@Language = "VBScript"%>
<%Option Explicit%>
<%
dim oRs,oConn,dateofleave,sql,uid
dateofleave = trim(request.querystring("leavedate"))
uid = trim(request.querystring("employeeID"))
set oConn = Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open(Server.MapPath("test.mdb"))
set oRs = Server.CreateObject("ADODB.Recordset")
sql ="INSERT INTO test (dateofleave)"
sql = sql & "VALUES ('&dateofleave') WHERE employeeID=&uid"
set oRs = oConn.execute(sql)
%>
can someone help me with this error?
Microsoft JET Database Engine- Error '80040e14'
Missing semicolon ( at end of SQL statement.
/test/booking.asp, line 18
where line 18 is "set oRs = oConn.execute(sql)"
View 4 Replies
View Related
Apr 10, 2004
Hey
I am trying to retieve a value from teh database and add one to it, then update the database with thenew value before redirecting to a page.
I am recieving this error and don't know why, i have the following coed below.
Dim objReaderQ as OleDBDataReader
Dim strSQLRead As String
Dim objCmd As New OleDbCommand
strSQLRead ="Select Quantity from tblCart Where (Productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "')"
objCmd = new OleDbCommand(strSQLRead, objConn)
objReaderQ = objCmd.ExecuteReader()
if objReaderQ.Read()
'update quantity by 1
Dim i as integer
i = objReaderQ("quantity")
i = i + 1
objReaderQ.Close()
Dim strSQLQuantity as String = "INSERT INTO tblCart (Quantity) VALUES (@quantity) WHERE (productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "');"
Dim objCmdQuantity As New OleDbCommand(strSQLQuantity, objConn)
objCmdQuantity.Connection = objConn
objCmdQuantity.Parameters.Add("@quantity", OleDbType.VarChar, 255)
objCmdQuantity.Parameters("@quantity").Value = i
objCmdQuantity.ExecuteNonQuery() ' <--- Error Is Occuring On This Line
Response.Redirect("ViewBasket.aspx")
end if
I really can't see what is wrong as i have placed the semi colon it wanted at the end of the string.
Thanks you for your time
Chris
View 1 Replies
View Related
Aug 3, 2007
Ok, here's my setup. I've got a named instance in a SQL 2000 cluster. I only have dbo rights on my database, because it is a shared infrastructure. Here's my current web.config connection string (the meat, anyway):
When I'm at the office, this is my connection string, pretty normal:
connectionString="Data Source=ServerNameInstanceName;Initial Catalog=blah..."
But, when I connect through the VPN, I can't just connect through the named instance - I have a specific port. This is where things get odd.
First, if I try to connect through SQL Server Management Studio (2005), i get nothing. If I try to connect using "ServerNameInstanceName, (comma) Port Number" it connects, but not to my instance. I get a seperate set of databases that I believe are in the default instance. So, I changed the comma to a semicolon (;) - and it still connected to the same thing - connected to the database, but to the wrong set of databases. So, on a whim, I tried plunking my string, which was now "ServerNameInstanceName;(semicolon) PortNumber" into the SQL 2000 Tools and it worked in both Query Analyzer and in Enterprise Manager. So, I thought, I'll just slam this into my connection string and all will be well. No. I can't use a semicolon in my connection string, and I can't find an escape character to use. Double semicolons don't work, a comma doesn't connect me properly, double colons don't work, the JDBC brackets don't work {} - so I'm at a loss. I'm out of ideas. I've set up aliases, and those don't work earlier.
I'm using ASP.net 2 with VB & C# and Visual Studio 2005 Professional. Thanks for any help anyone can give on this!
View 3 Replies
View Related
Aug 30, 2007
Hello friends,
I am designing an ETL process in which I have a source column which contains names seprated by semicolon( i.e Rajat Kr Sharma;Mr Sammer;Mr Ravi;Mr Ankur Bhatnagar) in each row.
ETL process should create n records,one for each single name in destination table as n numbers of name apprear
in each row of source table.
The number of names can vary in each row of source table as per no. of delimited character semicolon.
So Can some one suggest me to lay out some plan and what controls of SSIS I should use?
Here I m giving the pictorial view of activity I'm trying to carryout.
Source Table Row
______________________________________________________________________
|Rajat Kr Sharma;Mr Sammer;Mr Ravi;Mr Ankur Bhatnagar |
|_____________________________________________________________________ |
Changes to Destination Table
______________________________________
|Rajat Kr Sharma |
|_____________________________________|
|Mr Sammer |
|_____________________________________|
|Mr Ravi |
|_____________________________________|
|Mr Mr Ankur Bhatnagar |
|_____________________________________|
View 3 Replies
View Related
Apr 28, 2008
when I backup the SQL Server 2005 database ,always get prompt
Error description: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'E'.
+48166A04.0004 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
+48166A04.0004 [Microsoft][ODBC SQL Server Driver][SQL Server]The label 'E' has already been declared. Label names must be unique within a query batch or stored procedure.}
how to deal with it, thanks
View 5 Replies
View Related
Jun 20, 2008
Hi, I'd like to know if there's a way to get sql server NOT to ignore the colon when performing a full text search (CONTAINS) for a string "sometext:". At this moment the query works, only the results are not narrowed to the ones containing the specified colon. I've read about this and I saw that these kind of characters (word breakers and stemmers) are ignored and want to know if there's a way to work around this (obviously performing well - so LIKE fails the test). Thanks
View 2 Replies
View Related
Jan 7, 2012
This SQL is meant to show the changes that will be made, when removing a selected user's email address from a batch.
However, when executed, each row is duplicated, and in the duplication, the semi-colon or comma isn't removed. For example, if I wanted to remove user "sam@mail.com"
The table results displayed would be:
Row 1:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;frank@mail.com
NewParamValue: jack@mail.com;frank@mail.com
Row 2:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;john@mail.com
NewParamValue: jack@mail.com;;frank@mail.com
Ideally, it should only display each row once, and not have the semicolon error. It seems to be a union error, because when I comment out the First and second union statements, it runs fine.
-- Delete email address from a.Batch
IF(@EmailAddress IS NOT NULL)
BEGIN
IF(LEN(@EmailAddress) > 0)
BEGIN
IF(@ShowOnly = 1)
[Code] ......
View 2 Replies
View Related
Sep 17, 2015
How do i concatenate 24 columns with semicolon delimited into a single column without getting data conversion error ....
View 2 Replies
View Related
May 16, 2007
Here's a weird one:
We are setting up a job for the SQL Server Agent via SSMS. The Job Step Type is SSIS.
In the Job Step Properties window, on the Set values tab, you can enter Values to override your package variables - normally all well and good.
However in this particular case, the variable Value contains semicolons ( - it is a Connection String for an ODBC driver. Eg: Driver={Client Access ODBC Driver (32-bit)};system=MYSERVER;...
The behaviour for this Value is weird:
If the Value is not surrounded with double quotes ("), the job fails with "The command line parameters are invalid."
If the Value is surrounded with double quotes ("), the job will run as intended. The catch is: that entry and any subsequent "Set Values" entries disappear next time the Job Step Properties window is opened.
This looks like a bug with the parsing of those strings by the Job Step Properties window?
Or am I missing something?
Mike
View 5 Replies
View Related
Oct 4, 2004
Hi
Please tell me which delimiter to use. Criterion: 1) The delimiter should appear as if it were space in the Back end 2) while running reports it should not dampen the lay out 3) The delimiter should not be an enterable character
Ex: i tried with chr(10) but while running reports, the characters after the delimiter are shifted to new line.
The delimiter that i use, should not create such undesirable effects.
Thanks in Advance
View 1 Replies
View Related
May 9, 2006
Hi!
Im trying to import some data from a Flat File Source, where a row delimiter is {LF} and column separator is SPACE.
Data looks like this:
GI$0c2 T08b 1 1 20060508 000655 6 8 8 c0a81f1f 1 1 1 00A 3 24206816 3 24206816 1 1 3 59910000 001 1 3 14730050 0 25 F10 XX 317148-131136 loop TG_MRB 0 M027 1 3 0 20060508 000655 0 3 59910000 SIP
This is the first record that generates around 41 columns and sorts data as it should, but if the second record is smaller the row delimiter {LF} is ignored and put into one of the columns untill all 41 columns from previous record are filled. It seems to me that columns separator has the priority over the row delimiter which is very wrong. :). If there is a {LF} in the file that should mean that it needs to be in a new row as a new record. I try to keep this all in a SQL 2005 package without using any additional software, i know there might be a solution with the scripting component, but would like to see if theres someone with the similar solution before i start writing any scripts. (i dont like parsing strings with scripts from bulky files:))
Thanks!
Sebastijan L.
View 4 Replies
View Related
May 22, 2001
hello!
i was on with DTS, dumping a flat file to SQL DB and i came across an error which says: "Too many columns...; found whitespaces.."
When I checked the row that caused this, I found out that entries with ""s are split into two columns possibly because the backslashes were read as tab delimiters..
I've tried other delimiters, but they do not seem to fit because most of the entries have them.
Please advise. Thanks!
View 1 Replies
View Related
Feb 12, 2008
Hello--
I need to Ç as my field delimiter and Æ as my row delimiter in bcp script. I use the script below but I dont get the right outcome- HELP
bcp databse.dbo.table out table.dat -S myserver -U myserid -P mypassword -c -tÇ -r"Æ"
Josephine
Josephine
View 1 Replies
View Related
Mar 12, 2008
Hi,
I have a problematic file. It's vertical-bar delimited, but the file conn mgr can't find ANY of the pre-defined row delimiter types.
The data looks like this:
col1|col2|col3|||||||||
With a bunch of vertical bars on the end of each line.
How should I handle this file?
Thanks!
View 10 Replies
View Related
May 4, 2004
Hello,
Inside a column I have this result:
Record 1: Sales;Admins
Record 2: ;Sales;Admins
Record 3: Sales;
Record 4: Admins;
You can see the delimiter ";", it can be everywhere.
Now I want to delete "Sales".
Therefor I have to search where the "Sales" is. (records)
After that I want to delete the "Sales".
If I delete it the record may not have 2 or more delimiters after each other, like here:
Record 1: ;Admins (good, better is to remove the delimiter also)
Record 2: ;;Admins (bad)
Record 3: ;(good, better is to remove the delimiter also)
Record 4: Admins;
Can somebody help me how to build this query?
Thanks!
View 7 Replies
View Related
Nov 26, 2014
I have a PL/pgSQL Code like this:
[code=" CREATE OR REPLACE FUNCTION public.split_string(text, text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $function$
DECLARE
pos int;
[Code] ....
Its split a String with a delimiter. Like this
[code="select * from split_string('3.584731 60.739211,3.590472 60.738030,3.592740 60.736220', ' ');
"3.584731"
"60.739211,3.590472"
"60.738030,3.592740"
"60.736220""][/code]
My question is how i can save the first result in a temp_array (or table i dont know) so I can get the result and split up the results again with the delimiter ','.
View 1 Replies
View Related
Mar 11, 2006
Can somebody help me with a delimiter problem I have.I have several PIPE (|) delimted text files which I need to import toSQL.With one of the files I keep encountering the following error;"Error at Source for Row 27753. Errors encountered so far in this task:1. Column Delimter not found."I suspect the problem is that one record (and possibly more) has a PIPE(|) within a field, because some of the fields contain free text.Getting an export of the file again using a different delimter like tabor comma will not work as these characters occur throughout the file.I'm open to suggestions as to how to resolve this and really need toget a solution soon.One solution I was thinking of, but do not know how to execute is tocount the number of PIPEs on each record and then manually change therecords which have count which is inconsistent with the rest of thefile.I've also tried importing to Access first and then SQL, as this hasworked for me in the past, but it did not work.Regards,Ciarán
View 3 Replies
View Related
Jul 20, 2005
hi, i had been removed the row counts and the column spaces... but ifailed to remove the extra tabs between rows of data return from theosql output.how to detect the row delimiter?i noticed i can use bcp to have a more decent output file, but i don'twant to do too much of file read-write. the programming enviroment isquite easy to "capture" the output from the comand prompt.secondary, char(252), 253, 254, 255 is not being read by the commandprompt, for example, 253 turns into 132 when i decode the input.thankx.from alan.
View 1 Replies
View Related
Oct 26, 2006
Hi,
I get a string whihc looks like 'Q306/Q406 Version1/Current/Q108 Version2'
I need to split the above string and get each of those values... ' / ' delimiter
Can some one please help on this.
Thanks
View 7 Replies
View Related
Jul 25, 2007
I'm in a flat file connection manager editor and I have a flat file where the columns are separated by a space. Does anyone know how to specify a space in the column delimiter option? I've tried {' '} and {s} but these don't work. Not sure what the syntax is for indicating a space. Thanks in advance
View 1 Replies
View Related
Sep 14, 2007
Sorry if this thread has been repeated but I cannot find a reference to my problem. I have read that apostrophes can be qualified with double quotes but that doesnt solve my problem. I am using SQL 2000 on a Windows 2000 Server trying to set up a job to email the results of a query. I have had no trouble getting it to run as scheduled but cannot seem to get the results out when I add a clause that filters out a text column.
This is the query I am trying to run from a step within a job inside our SQL Agent:
exec master.dbo.xp_sendmail
@recipients = 'user@mycompany.com',
@subject = 'Overnight Safedata Statistics',
@query =' SELECT u.username as BackUpSet , r.requestresultstatus as Status, r.requesttype
from users u ,requests r where u.userid = r.userid
AND requestdate between getdate()-1 and getdate()
AND requestresultstatus <> 'Success' ',
@message = 'Results over the last 24 Hours',
@attach_results = FALSE,
@dbuse = 'BackupServer'
I can successfully run the @query string in query analyzer and it worked fine from SQL mail until I added the last clause to the step (AND requestresultstatus <> 'Success' ). I do not know how to get the 'Success' treated as text.
Any advice would be appreciated.
Thanks
JT
P.S. I am a network guy trying to give our SQL developers a break.
View 5 Replies
View Related
Nov 2, 2015
The input is like this : xxxxxxxx=yyyyy=key_id=12345xyxyx
I would like to use the right function and get 12345xyxyx alone. The function must search for the first '=' symbol from the right of the word and get the characters from the end till the '=' symbol.
View 2 Replies
View Related
Jul 18, 2007
Hello,
Does any one know a simple way of changing the delimiter value for a CSV report delivered via email from SQL Server 2000 Reporting Services? The default is comma.
From the research I've done it seems that it can be done by writing a VB script that calls the Render method, but I'm not a developer and it seems crazy to have to go to those lengths just to change one attribute of a rendering extension that is available out-of-the-box.
I hoped that I would be able to change a value in a config file (e,g, RSReportServer.config), but this seems not to be the case.
Thanks
Lempster :S
View 1 Replies
View Related
Feb 1, 2006
Hi,
I'm trying to design this package where i take data from a source and need to transform it into a flatfile with some extra static information.
I use a SQL script like this (ex.):
SELECT '
BS0220131264202400000130001'+cast(wa.perf_applicant_number as nvarchar)+'000000000' + wa.perf_firstname + ' ' + wa.perf_lastname + CHAR(13)+
'BS0220131264202400000330001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000' + WAPD2.strvalue+ '
BS0520131264202410001130001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000 tekst der skal stå på kortet' as nvarchar
FROM dbo.WAIT_Applicant WA (nolock)
This makes the text (from one record) split up over several lines in the output.
I succeded with this in a SQL2000 DTS package and the flat txt-file looked liked I wan't it to. But now i tried doing it in 2005. And now it is not workin' anymore
In my Flat File Connection Manager Editor i chose {LF} as the row delimiter and the preview looks really nice. Like this:
BS0220131264202400000130001000000015826727000000000Søren Hesth
BS0220131264202400000330001000000015826727000000000adfasdf
BS0520131264202410001130001000000015827207000000000 tekst der skal stå på kortet
But in the file that is created it doesn't split up over several lines. Instead of a carriage return it puts a [black box] - a sign which counts as the carriage return.
I don't know if I have explained this well enough, but I hope that someone can help me. I've been trying for 3 days now.
View 4 Replies
View Related
Nov 2, 2015
I want to extract two strings from xxxxx - yyyyyy separately as xxxxx and yyyyyy. The source always has two strings brought together with a - symbol. How to extract these two strings.
View 4 Replies
View Related
Mar 8, 2004
Hello Everyone,
Hope someone will be able to help me out here.
I have a text file exported from my DTS package and it requires an '!' as a custom column delimiter.
Does anyone have any idea how I can use the '!' mark instead of the Tab or Vertical Bar as my Column Delimiter?
Would appreciate any suggestions.
Thanks,
Kay
View 3 Replies
View Related
Jan 24, 2008
Hello Folks,
I have a importfile that I need to insert into an db table. The file looks like this:
one;two;three;text;moretext
one;two;three;text;moretext;
one;two;three;text;moretext
one;two;three;text;moretext;
one;two;three;text;moretext
one;two;three;text;moretext;
As you can see some rows contains a delimiter while others dont. There is a programing error on the application that generates the file and this cannot be changes.
Is there a way in integration services to remove the delimiter ?
Thanks
holts
View 8 Replies
View Related
Aug 14, 2006
i am running sql server 2000 on windows 2000. i have a need to export a view and delimit the columns with double quotes as it has imbedded commas in the columns, how do i do this??
View 1 Replies
View Related
Feb 16, 2015
I have a date file with no delimiter like bellow
0080970393102312072981103378000004329392643958
0080970393102312072981103378000004329392643958
I just know 5 first number in a line is for example "ID of bank"
or 6th and 7th number in a line is for example "ID of employee"
How can I create a XML format file?
View 2 Replies
View Related
