0

I can easily remove html tags from string using regex (<[^>]*>) but that keeps the text inside the html tags. I have sql insert statement like this :

INSERT INTO `table1` (`id`, `name`, `location`, `description`, `whenadded`, `presentlocation`, `sponsor`, `participant`, `link`, `country`, `proffesion`) VALUES
(1, 'Abdulsalami</title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arr', 'Abubakar</title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div>', '<P>General Abdulsalam Abubakar (born June 13, 1942) became Nigeria''s eighth military Head of State in 1998, after Ironsi, Gowon, Murtala, Obasanjo, Buhari, Babangida and Abacha, since the country''s independence from Britain in 1960.</P>\r\n                                          </title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div>', '0000-00-00 00:00:00', '</title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:', '</title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div>', 'Male</title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div>', '', '</title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:', '</title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.abdj{position:absolute;clip:rect(452px,auto,auto,452px);}</style><div class=abdj>Depending on which state <a href=http://arropaydayloans.com >payday loans</a> cap on our money.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div></title><style>.a6js{position:absolute;clip:rect(391px,auto,auto,391px);}</style><div class=a6js>Use more credit sitting <a href=http://kotikpaydayloans.com >payday loans</a> it solve their experiences.</div>'),

There is one common thing in all my records that it always starts with </title and end with '

So I need a regex that will remove every from </title to ' but keeping '. Because each column ends is different.

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
skcrpk
  • 558
  • 5
  • 18

1 Answers1

-2

You can use replace method while inserting like (run in sqlserver side )

declare @str varchar(max)= ( 'Abdulsalami</title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arr'', ''Abubakar</title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>Spending time doing you <a href=http://arropaydayloans.com >payday loans</a> making a credit before.</div></title><style>.ag8o{position:absolute;clip:rect(434px,auto,auto,434px);}</style><div class=ag8o>')

select REPLACE('abcdefghi','b','f'),
replace( REPLACE(@str,'<title>','''') ,'</title>', '''')

In this situation still you can use parameter inline query 

using(SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();

    SqlCommand command = new SqlCommand(InsertStmtUsersTable, conn);
    command.CommandType = CommandType.Text;

    command.Parameters.Add(new SqlParameter("username", userNameString));
    command.Parameters.Add(new SqlParameter("password", passwordString));
    command.Parameters.Add(new SqlParameter("email", emailString));
    command.Parameters.Add(new SqlParameter("userTypeId", userTypeId));
    command.Parameters.Add(new SqlParameter("memberId", memberId));
    // Rest of your Parameters here

    var result = (int)command.ExecuteScalar();
}

or 

using (SqlConnection dbConn = new SqlConnection(connectionString))
{
    dbConn.Open();

    using (SqlTransaction dbTrans = dbConn.BeginTransaction())
    {
        try
        {
            using (SqlCommand dbCommand = new SqlCommand("insert into [DB].[dbo].[User] ( [Id], [AccountId], [FirstName], [LastName], [JobTitle], [PhoneNumber] ) values ( @id, @accountid, @firstname, @lastname, @jobtitle, @phonenumber );", dbConn))
            {
                dbCommand.Transaction = dbTrans;

                dbCommand.Parameters.Add("id", SqlType.VarChar).Value = id;
                dbCommand.Parameters.Add("accountid", SqlType.VarChar).Value = accountId;
                dbCommand.Parameters.Add("firstname", SqlType.VarChar).Value = firstName;
                dbCommand.Parameters.Add("lastname", SqlType.VarChar).Value = lastName;
                dbCommand.Parameters.Add("jobtitle", SqlType.VarChar).Value = jobTitle;
                dbCommand.Parameters.Add("phonenumber", SqlType.VarChar).Value = phoneNumber;

                dbCommand.ExecuteNonQuery();
            }

            dbTrans.Commit();
        }
        catch (SqlException)
        {
            dbTrans.Rollback();

            throw; // bubble up the exception and preserve the stack trace
        }
    }

    dbConn.Close();
}

Sql inline query with parameters. Parameter is not read when the query is executed

Executing query with parameters

Community
  • 1
  • 1
Ajay2707
  • 5,690
  • 6
  • 40
  • 58