Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Getting data based on filtering CharIndexes

Answered Getting data based on filtering CharIndexes

  • Monday, February 11, 2013 10:32 PM
     
      Has Code
    Sign In to Vote
    0

    I have a column that looks like this:

    #

Name = Abc

Id=15

=

#

# Start

#

10312,20151027,2106883456,0007639234
103121,20151028,21068836342,0007639279
#

# End

#

    What I want is, i want to insert the valid data to 4 new column in my new table. So my final result will look like this:

    T2.Col1=10312

    T2.Col2=20151027

    T2.Col3=2106883456

    T2.Col4=0007639234

    How can I parse this data out for each column?

     

All Replies

  • Monday, February 11, 2013 10:43 PM
     
      Has Code
    Sign In to Vote
    0

    That column.  Is it in SQL? or some text file

    SQL does not have a native way to split files but there are 2 functions that will be effective in helping you reach the goal of splitting a string.

    CharIndex() and substring() functions are your friend but using them can be hard on the eyes.  This guy made it easier by turning it into a function:

    FROM: http://sqltutorials.blogspot.ca/2007/09/sql-function-split.html

    DECLARE @NextString NVARCHAR(40)
DECLARE @Pos INT
DECLARE @NextPos INT
DECLARE @String NVARCHAR(40)
DECLARE @Delimiter NVARCHAR(40)

SET @String ='SQL,TUTORIALS'
SET @Delimiter = ','
SET @String = @String + @Delimiter
SET @Pos = charindex(@Delimiter,@String)

WHILE (@pos <> 0)
BEGIN
SET @NextString = substring(@String,1,@Pos - 1)
SELECT @NextString -- Show Results
SET @String = substring(@String,@pos+1,len(@String))
SET @pos = charindex(@Delimiter,@String)
END

    cs

     
  • Monday, February 11, 2013 10:50 PM
     
     
    Sign In to Vote
    0
    We get the files like these and I need to load this type of data in to my tables. So, my idea was lets load that entire file in to a single column in my table and parse that data out.
    • Edited by SqlCraze Monday, February 11, 2013 10:52 PM
    •  
     
  • Monday, February 11, 2013 10:56 PM
     
     
    Sign In to Vote
    0
    Why not parse before you load? There are certainly better languages to parse text like this than T-SQL. If you already have the data in the table, I would recommend that you use a CLR stored procedure or a CLR table-valued function.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

     
  • Monday, February 11, 2013 11:03 PM
     
      Has Code
    Sign In to Vote
    0

    I don't know anything about CLR. I was thinking of doing something like this:

    Select	LTRIM(RTRIM(Substring(PARSENAME(Replace(T.Col,',','.'),4),
				Charindex(',',PARSENAME(Replace(T.Col,',','.'),4))+1,
				LEN(PARSENAME(Replace(T.Col,',','.'),4))))) as Col1
--Gives wrong values, but, something on these lines

     
  • Tuesday, February 12, 2013 1:49 AM
     
     
    Sign In to Vote
    0

    You could do this by loading it into the DB first but parsing it out before bringing it into the DB is the way to go for several reasons:

    1) It is the standard way

    2) There will be situations that SQL will not handle

    3) Sometimes you will not be able to load the original into the DB at all (see bad data).

    4) it is easier to do.  A primary purpose of powershell is to parse out data

    5) the SQL will be ugly...oh so ugly.  I see powerShell in your future

    cs

     
  • Tuesday, February 12, 2013 7:42 AM
     
     
    Sign In to Vote
    0

    So what language do you use in your organisation for applications? Whatever you use, read the file in a program in that language and parse the text. If you have access to regular expressions, this will be a lot easier. I don't know SSIS myself, but that is certainly an option to consider.

    You might be able to write something in T-SQL, but then a month later there is some variation and your code breaks down.

    If you only had these comma-separated lists of numbers it would be one thing, but then there also these # and whatnots.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

     
  • Tuesday, February 12, 2013 1:51 PM
     
     
    Sign In to Vote
    0
    SSIS will require a cleaner file.  The OPs is certainly dirty.
     
  • Tuesday, February 12, 2013 4:25 PM
     
     
    Sign In to Vote
    0
    Any good articles on using CLR for this problem? CountryStyle, do you suggest using the CLRs as well?
     
  • Tuesday, February 12, 2013 10:56 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Here is a quick example of implement a CLR procedure as such in SQL Server. There is no string parsing or anythihing; I'm just demonstrating the mechanisms. To actually parse the data you would probably use the RegEx classses in .Net, but they are not really on-topic for this forum. (And more importantly, I would have to spend quite some time with the .Net Fx Reference, as I'm not a fluent .Net programmer myself.)

    Here is a very simple (and not particularly meaningful C#) file:

    using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class myclrtest
{

[Microsoft.SqlServer.Server.SqlProcedure]
public static void myclr(
                                     SqlString     sqlcmd)
{
        // Connect through the context connection.
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
                SqlCommand command = new SqlCommand();
                connection.Open();
                command.Connection = connection;
                try {
                        command.CommandText = sqlcmd.ToString();
                        command.ExecuteNonQuery();
                }
                catch {
                        command.CommandText = "PRINT 'Entering CLR catchhandler'";
                        SqlContext.Pipe.ExecuteAndSend(command);
                        throw;
                }
        }
}

};

    The purpose of this file is just to run an SQL comand through the CLR (I wrote it to test what happens if you do).

    Put this file in C:\temp on your computer as myclr.cs. Compile it as:

    csc /target:library myclr.cs

    You need to have a C# compiler in the path. There is always one in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

    Now you move over the SQL Server. First you need to load the dll with the CREATE ASSEMBY statement:

    CREATE ASSEMBLY minierrortest FROM 'C:\temp\myclr.dll'

    I'm here assuming that you have SQL Server on your machine. If not, you need to move the DLL.

    Next you create the stored procedure this way:

    CREATE PROCEDURE myclrest
                                 @sqlcmd    nvarchar(MAX) AS
    EXTERNAL NAME myclr.myclrest.myclr

    This three partname consists of the assembly, the namespace and the name of the actual method.

    Now you can use this stored procedure like any other stored procedure.

    Note: rather than compiling the DLL manually, you can deploy it through Visual Studio, if you are able to figure out. (I am not; VS goes over my head.)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Wednesday, February 13, 2013 4:36 AM
     
     
    Sign In to Vote
    0
    Any good articles on using CLR for this problem? CountryStyle, do you suggest using the CLRs as well?

    I have not used CLR so I can't say.  There might be advantages.

    I am in favor of a common foot print where it is practical so my vote goes with stock with what you know or your workplace is using that is not antiquated.