SQL 2012 :: Second Delimiter And Converting
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
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
Jun 2, 2014
I am running sqlserver 2012.
My output data is like
COL1
aaa
bbb
ccc
Now, i want to convert the output to
aaa;bbb;ccc
How can i do this?
View 2 Replies
View Related
Jun 16, 2015
Convert 100 xml files individually to pdf's and zip them in a folder along with the source files.
Can it be possible in SQL server BI world?
If possible make this an automated process for every 100 files.
View 3 Replies
View Related
Jun 16, 2015
I'm knew to Sql, I've tried many variations, and I keep getting errors:
CAST(ROUND(IP_BLa.MEAS_VALUE-32)*5/9,2)
CAST(ROUND(IP_BLa.MEAS_VALUE-32)*5/9,2)as decimal(3,2))
View 1 Replies
View Related
Jul 15, 2015
I'm struggling to modify the T-SQL query below to return the results as XML.
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119, 122);
The XML result document should have the following structure:
<Root>
<ProductModelData id="119">
<Name>Bike Wash</Name>
</ProductModelData>
<ProductModelData id="122">
<Name>All-Purpose Bike Stand</Name>
</ProductModelData>
</Root>
View 2 Replies
View Related
Feb 17, 2014
I have a very large database running in a production environment. The backup files are getting to be very difficult to manage. They are at 70gb as of now and growing at a rate of 10gb per month. This is due to document images being stored in the database in one table. I have read a little about a new feature in SQL Server 2012 called Filetable. Can we migrate to SQL Server 2012 and convert to the filetable structure, will this decrease the size of the backups? Would backup of the document images be taken care of by our nightly file system backups now?
View 1 Replies
View Related
May 13, 2014
Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.
View 3 Replies
View Related
Jun 9, 2014
I'm trying to go deeper into SQL Server's XML support. When looking at a recent forum post, I got curious to see if I could achieve the same result with XPath and FLOWR. Here's the data I'm working with:
create table #data (myXMLData xml)
insert into #data(myXMLData) values (
'<?xml version="1.0" encoding="utf-8"?>
<order>
<Product Index="e1e2c499-f9...etc...." ProductID="12" >
<Attribute Name="Paper...etc..." />
[Code] ....
Here's the working XPath query and results:
select data.mynodes.value('@Name', 'varchar(50)') Name
from #data
cross apply myXMLData.nodes('/order/Product/Drops/Drop[@Number="1"]/Area') data(mynodes)
Results:
Name
43001PBOX
[Code] .....
Here's my first attempt at a FLWOR expression. Note that doesn't produce the same results.
select myXmlData.query('
for $drop in /order/Product/Drops/Drop
for $Name in $drop/Area/@Name
where $drop/@Number=1
return data($Name)
')
from #data
Results:
43001PBOX 43001R001 43001R002 43023PBOX 43023R001 43023R002 43031PBOX 43031R001
How to build a FLOWR-based query that produces the same result as the pure XPath one?
View 1 Replies
View Related
Mar 19, 2015
I am using a custom sql query to import data into Tableau. Problem is I need to change the varchar column data in SQL currently returning 18/01/2014 08:35:13 as a format into the date format 05/21/2014 3:55:51 PM before I can use it.
View 9 Replies
View Related
Jul 24, 2015
I have a column name DateofRecord and it is nvarchar type..all the values in this column are like this
"04/24/2013'
"05/01/2014"...etc...
My requirement is to convert this column into Datetime ?
I tried so many ways using cast and convert functions like cast(dateofrecord,datetime) or like convert(datetime,replace(DateofRecord,'"','''')) ..it didnt worked..
View 9 Replies
View Related
Dec 9, 2013
difference between
SELECT ROUND ('6.465',2) --- result 6.46
and
SELECT ROUND (6.465,2) --- result 6.47
with
It's because you're relying on an implicit conversion from a string to a decimal data type which SQL server will do to 2 decimal places by default...
Alright:
SELECT ROUND (CONVERT(DECIMAL(3,2),'6.465'),2) --- result 6.47 Now please explain this:
SELECT ROUND('0.285',2) -- 0.28
SELECT ROUND(0.285,2) -- 0.29
SELECT ROUND (CONVERT(DECIMAL(3,2),'0.285'),2) --- result 0.29 The string value does not seem to be converted to decimal with 2 decimal places.
MS is on the safe side with mentioning the last digit is always an estimate But because the result of the estimate is always the same, I would like to know:
* how is a string value exactly implicitly converted?
* how exactly does the estimation work, that in case of doubt rounds a value up or off?
View 2 Replies
View Related
Dec 19, 2013
Within in Visual Studio 2012 solution, I have several projects, one of which is a Database project. I am defining several tables. The one in question is:
CREATE TABLE [dbo].[tblKppHierarchyPcl]
(
[ID] NUMERIC(18,0) NOT NULL,
[Name] VARCHAR(500),
[PartStructureKey] NUMERIC(18,0) NOT NULL,
[PartNumber] VARCHAR(500) NOT NULL,
[ParentPartNumber] VARCHAR(500) NULL,
[code]...
Error SQL72014: .Net SqlClient Data Provider: Msg 245, Level 16, State 1, Line 76 Conversion.failed when converting the varchar value 'Coolant Quick Disconnect' to data type int.So it has a problem with inserting 'Coolant Quick Disconnect' into the Name column. The Name column is CLEARLY a varchar column but somehow it thinks it's an int column.
View 8 Replies
View Related
Jun 5, 2014
I have a lot of rows of hours, set up like this: 0745, 0800, 2200, 1145 and so on (varchar(5), for some reason).
These are converted into a smalldatetime like this:
CONVERT(smalldatetime, STUFF(timestarted, 3, 0, ':')) [this would give output like this - 1900-01-01 11:45:00]
This code has been in place for years...and we stick the date on later from another column.
But recently, it's started to fail for some rows, with "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value".
My assumption is that new data being added in is junk. If I query for these values and just list them (rather than adding a column to convert them also) that's fine, of course. I've checked all the stuffed (but not yet converted - so 11:45 rather than 1145) output to see if it ISDATE(), and it is. There are no times with hours > 23 or minutes greater than 59 either.
If I add the CONVERT in, we see the error message. But here's the oddity, if I place all of the rows into a holding table, and retry the conversion, there is no error. It's this last bit that is puzzling me. Plus I can't see any errors in the hours data that would cause a conversion problem.
I've put the whole of this into a cursor to try to trap the error rows too, but all processes fine. Why would it fail if NOT in a cursor?
View 9 Replies
View Related
Jul 22, 2014
I am trying to create hierarchyID for a directory file path.
View 1 Replies
View Related
Oct 30, 2014
I have been trying to convert datetime but keep getting this error(Conversion failed when converting date and/or time from character string.It works just fine if I don't use execute sp_executesql,.
<code>
DECLARE @tablename AS nvarchar(max)
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @startdate datetime
[code]....
View 5 Replies
View Related
Sep 21, 2015
I want to save 999999999 as real data in sql.but it saved 1+E09.
how can I save 999999999?
View 9 Replies
View Related
Nov 3, 2015
I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.
For examples
CASE
WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1
WHEN PracticeCode IN (.......) THEN 1
WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1
ELSE 0
END
I need to put these conditions in config table and generate dynamic sql.
What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.
View 2 Replies
View Related
Jan 31, 2014
I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname
2013 11 ACB
2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
View 3 Replies
View Related
Mar 23, 2015
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
View 6 Replies
View Related
Aug 7, 2014
I have a large excel spreadsheet created by finance user that contains several decades worth of sales data.
Here is a small sample:
Guest Count
Unit ID1/2/2011 1/9/2011
3 0
7 0
8 0
90 0
151696 1202
222769 1914
232704 2110
250 0
282838 1882
331089 691
363581 3064
371469 1062
I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.
The desired output:
Unit IDDate Guest Count
31/2/2011 NULL
71/2/2011 NULL
81/2/2011 NULL
91/2/2011 0
151/2/2011 1696
and so on ......
The spreadsheet has 2900 columns and 3500 rows so performance is definitely a consideration as well.
View 9 Replies
View Related
Aug 12, 2014
I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.
I am also including example of how the account_numbers are formatted.
R222209
R222220
R222222
R222212
R221123
F707768
[Code] .....
View 5 Replies
View Related
Aug 6, 2015
Am converting varchar field to float and summing using group by and next inserting to varchar field(table).
while inserting float value it is converting to exponential ex:1.04177e+006 but if i execute only select statment actual float value will get display ex:1041765.726
My question is why it is converting while inserting ? and how to avoid it.
select query : SUM(CONVERT(float,(rtrim(REPLACE(REPLACE( column1, CHAR(13), ' '), CHAR(10), ' '))))) as AggregateValue
View 4 Replies
View Related
Jun 11, 2014
So I have two tables with similar columns etc, unfortunately the Insert to the History table fails. Noto sure how to format.
Source Table.
CREATE TABLE [dbo].[SKUCURRENT](
[UID] [int] IDENTITY(1,1) NOT NULL,
[SKU] [nvarchar](100) NOT NULL,
[WHSELOC] [nvarchar](50) NOT NULL,
[WEIGHT] [nvarchar](50) NOT NULL,
[Code] ....
View 1 Replies
View Related
Oct 30, 2014
OK, so I have this query where I'm trying to subtract values like this, when I do this I am getting (Arithmetic overflow error converting varchar to data type numeric.) I have tried many different things, and now of these work, it'll either return 0 because it loses the .XXXXX.
Convert(DECIMAL(10,7),CAST([TIME_OF_COMPLETION] as DECIMAL(10,7)) - Convert(DECIMAL(10,7),CAST([OPR_DELIVERED_TIME] as DECIMAL(10,7)) round(cast(cast(hist.[TIME_OF_COMPLETION] AS float) as DECIMAL(15, 5)) - CAST(hist.[OPR_DELIVERED_TIME] AS FLOAT),1 SELECT convert(FLOAT,CAST('735509.00053' AS DECIMAL(10,5))) - convert(FLOAT,CAST('735509.00047' AS DECIMAL(10,5)))
View 1 Replies
View Related
Aug 14, 2014
I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
View 3 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
Dec 26, 2014
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
View 2 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
