Wednesday, October 9, 2013

[T-SQL] splitting a string into columns

[T-SQL] splitting a string into columns

splitting a string into columns

Posted: 08 Oct 2013 02:53 AM PDT

Hi All , i am having a string i want output in 3 separate columns 'A-111:B-2222:C-33333' A B C 111 2222 333333'A-111:B-2222' A B C 111 2222 -'A-111'A B C 111 - -'B-2222' A B C - 222 -'B-2222:C-33333' A B C - 222 33333 How can i best achieve this using STUFF or by using split function in SQL Server. ThanxVD

BCP text out that includes quotation marks

Posted: 01 Oct 2013 04:45 AM PDT

I'm trying to export a text string that includes " to a text file using BCP.This code works as expected by exporting the word blah into a text file on my C drive:[code="sql"]-- Turn on cmdshell EXEC sp_configure 'xp_cmdshell', 1reconfiguregoDECLARE @cmd varchar(1000)SET @cmd = 'bcp "SELECT ''blah''" queryout "C:\bcpout.txt" -w -T'EXEC master..xp_cmdshell @cmd[/code]However if I change my text string from 'blah' to include quotation marks so it reads 'blah"blah', it fails to do anything.[code="sql"]DECLARE @cmd varchar(1000)SET @cmd = 'bcp "SELECT ''blah"blah''" queryout "C:\bcpout.txt" -w -T'EXEC master..xp_cmdshell @cmd[/code]Is there a way I can get the quotation marks exported to my text file using BCP?

Bulk insert with format file - handling quotation mark text qualifiers in header row

Posted: 08 Oct 2013 10:57 PM PDT

I'm trying to use BULK insert a flat file.The file has a header row. In addition to the delimiter character which is [code="plain"],[/code] the header row contains text qualifiers: [code="plain"]"[/code]The text file looks like this when opened in notepad:[code="plain"]"Column1Name","Column2Name","Column3Name""Row2Column1Data","Row2Column2Data","Row2Column3Data"[/code]I would like to use the bulk insert command with the format file option to import the data into a table in my database.The table in my database looks like this:[code="sql"]CREATE TABLE [dbo].[BulkInsertedData]( [Column1Name] [nvarchar](4000) NULL, [Column2Name] [nvarchar](4000) NULL, [Column3Name] [nvarchar](4000) NULL) ON [PRIMARY][/code]If I try to import from a flat file that is identical in all respects except that does not contain delimiters, I can import without any problems. Without delimiters, the format file looks like this:[code="plain"]<?xml version="1.0"?><BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RECORD> <FIELD ID="Column1Name" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4000"/> <FIELD ID="Column2Name" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4000"/> <FIELD ID="Column3Name" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="4000"/> </RECORD> <ROW> <COLUMN SOURCE="Column1Name" NAME="Column1Name" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="Column2Name" NAME="Column2Name" xsi:type="SQLVARYCHAR"/> <COLUMN SOURCE="Column3Name" NAME="Column3Name" xsi:type="SQLVARYCHAR"/> </ROW></BCPFORMAT>[/code]The bulk insert command I use to get the data in looks like this:[code="sql"]BULK INSERT BulkInsertedData FROM 'E:\1.txt' WITH ( FORMATFILE = 'E:\FormatFile.txt', FIRSTROW = 2, -- first row has column headings KEEPIDENTITY )[/code]My problem is - I can't figure out how to change the format file so that the bulk insert works when the header data is surrounded by text qualifiers as per the text file described above. I'm guessing the problem is escaping certain characters, but after hacking at it for a few hours, I haven't been able to figure out where to put the escape characters. Has anyone else successfully done this and if so, how did your XML format file differ from mine?

Convert comma to single quotes

Posted: 16 Aug 2012 09:15 PM PDT

Hi Experts:This is my table [code="sql"]Condition Columnnameakraft,crunckel TNAL,AZ StateAtlanta,Austin-San Marcos MACaney,aventura Area Alexandria, VA,Arlington, TX MarketAlpharetta,Alexandria City001,002 StoreSSameStore,HeitmanI Store TypeInLast6Months,CS-CC Space Types[/code]I want to convert the column like as [code="sql"]Condition Columnname'akraft','crunckel' TN'AL','AZ' State'Atlanta','Austin-San Marcos' MAC'aney','aventura' Area 'Alexandria', 'VA','Arlington', 'TX' Market'Alpharetta','Alexandria' City'001','002' Stores'SameStore','HeitmanI' StoreType'InLast6Months','CS-CC' SpaceTypes [/code]To add the single quotation to start and End of the position .. any one help to achieve this goal ThanksFAJ

updating a column based on a min(value) in the where clause

Posted: 08 Oct 2013 08:48 AM PDT

I would like to update a column in a table from a field in another table but based on the minimum value of a column, exampletable: carinfoid, car, color, rank1, vw, grey, 11, vw, white, 21, vw, red, 31, vw, blue,42, audi, black, 12, audi, white, 2into a table so the query i have is as follows:update table cars set color = color from carinfo where cars.id = carinfo.id and [min (rank)?]i can't seem to figure it out, any help is appreciated
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog