Function to Split one column to many

Suplied Delimiter and Array index, we can get multiple column that are specified or as many as there were..

How To Split column In SQL

just found a good script://sqlservercodebook.blogspot.com/2008/03/how-to-split-column-in-sql.html



Suppose you have a column in a table that has multiple pieces of information in it that
I need to break out into various columns. The column is random but the
values I need to separate out are the number and the UN number as below:

245 HELIUM, COMPRESSED 2.2 UN1046


I need to separate the 2.2 and the UN1046 into different columns. How do I
parse this?
CREATE TABLE Inventory (ItemDescription VARCHAR(99))
INSERT Inventory VALUES ('245 HELIUM, COMPRESSED 2.2 UN1046' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 6.6 UN99' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 446.6777 UN9988888' )
INSERT Inventory VALUES ('24adada5 HEdUM, sdsdsd 446.0 UN9988' )


SELECT RIGHT(ItemDescription,PATINDEX('% %',
REVERSE(ItemDescription))-1) AS COL1,
LTRIM(REVERSE(LEFT(REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription))))),
PATINDEX('% %',REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription)))))))))
AS COL2
FROM Inventory

What Every Developer Should Know About Database Scalability

Check out this SlideShare Presentation:

What Every Developer Should Know About Database Scalability

View more presentations from jbellis.

SP_FIND for SQL SERVER

CREATE PROC dbo.sp_find(@LikeSearchstr VARCHAR(255))
  AS
  /*
  Created by:    Abhishek Joshi
  Created Date:  Oct 27 2010
 
  Usage: To find a object stored proc or views that contain specific sttring.
 
  Example
  Exec dbo.sp_find 'Orders'
 
  */
  BEGIN
 DECLARE @cmd VARCHAR(MAX)

 SET @cmd='
 SELECT Name,OBJECT_DEFINITION(OBJECT_ID) as Text_Definition
 FROM sys.objects
 WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'+@LikeSearchstr+'%'''

EXEC(@CMD)
  END
 

Combining Like Searche with in search

while writing SP for Reports

1. MultiValue Parameter with Exact search Criteria:

There are conditions where you have have to pass parameters which are
multivalues usually string of comma separated values like @ProductID='Ram, Krina,
Hanuman'.

When we have to do exact search on these parameters we can directly use a
where clause like

p.ProductID in (Select _ID from dbo.fnSplitString(@ProductID,','))

where fnSplitString is splits values of parameter into table. exact search
can be performed. (code at the bottom)

2. Single Value Parameter with like Search.

In this situation we pass string parameter with a single value and
pass value like @Name='Ram'
the where clause will be

st.swName like (@Name+'%')

and all the names starting with Ram will be returned

3. Multi Value Parameter with like Search.

Now searching on this option how to do this. in nSQL



Code for fnSplitString

CREATE FUNCTION [dbo].[fnSplitString]
( @InputString varchar(8000), @SplitChar char(1) )

RETURNS @MyTbl TABLE(_ID varchar(4000))ASBEGIN

declare @LENInputString as intdeclare @x as int
declare @SingleChar as varchar(4000)

set @LENInputString = len(@InputString)
set @x = 0
while (select @x) < @LENInputString
begin set @SingleChar = SUBSTRING(@InputString, @x, 1)
if( select @SingleChar ) <> @SplitChar AND (select @InputString) <> ' '
begin
declare @y as int
set @y = @x+1
while (select @y) < @LENInputString+1
AND (select SUBSTRING(@InputString, @y, 1)) <> @SplitChar
begin
set @SingleChar = @SingleChar + SUBSTRING(@InputString, @y, 1)
set @y = @y+1
end
set @x=@y
end

if( select @SingleChar ) <> @SplitChar AND (select @InputString) <> ' '
begin
set @SingleChar = ltrim(rtrim(@SingleChar))
INSERT INTO @MyTbl (_ID) VALUES (@SingleChar)
end
set @x = @x+1
end
return
end

Index and length must refer to a location within the string. Parameter name: length (mscorlib)

While importing Data from Excel 2007 (a .xlsx file)

I came across following error

Index and length must refer to a location within the string.
Parameter name: length (mscorlib)

I was able to read meta data of excel but could not read the Data.

I was able to read data by copying over the same data to new Excel sheet and reading from there....This show me that excel's Internal database got infected in some manor.

while I am trying to find a better solution above work arround can be used.

Xcopy

xcopy "C:\Documents and Settings\t_AbhishekJ\My Documents\Citirx\CreateDDL" "v:\My Documents\SQLScripts" /s /y


Options
/A - Copies only files that have been set as archive files (using the ATTRIB command). The copied files will still be marked as archive files in BOTH the source and destination files.

/D:(date) - Copies only those files in the source directory that have been changed on or LATER than the specified date.

/S - Copies all files in the current directory and in any subdirectory within it.

/E - Copies subdirectories, even if they are empty. If you use this option, you must use the /S option also.

/M - Copies the same marked archive files as specified in the /A option; however, the files on the destination disk are no longer marked as archive files. This switch checks the archive attribute of a file: if the file`s archive attribute is set to off (-A), the file will not be copied.

/P - Does not copy the file until you respond to a prompt that asks you to confirm that you want the file copied. However, if you are using the /E option, you will not be prompted before an empty subdirectory is copied.

/V - Each file copied is verified to be sure the file stored on the destination disk is identical to the original on the source disk.

/W - Copies only after you respond to a prompt to begin copying (if this option is omitted, the copying begins as soon as you enter the XCOPY command and press the Enter key).

/Y - Causes XCOPY to replace existing files without a confirmation prompt.

/-Y - Causes XCOPY to give you a confirmation prompt before replacing an existing file.