SQL Server Get The Alpha Numeric Text Only From A Column

Lets say there is column with special characters and all. Will say you just want to filter and take the Alpha Numeric Text Only. This scenario comes normally when you want to find duplicate records, just like my case.

In this case you can simply right a SQL Function to filter the text.

1 2 3 4 5 6 7 8 9 10 11 12

CREATE Function [dbo].[GetAlphanumericTextOnly](@Temp VarChar(1000)) Returns VarChar(1000) AS Begin Declare @KeepValues as varchar(50) Set @KeepValues = '%[^a-z0-9]%' While PatIndex(@KeepValues, @Temp) > 0 Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '') Return @Temp End

Then call the function

1	SELECT [dbo].[GetAlphanumericTextOnly]('Nifal.Nizar1990@Hotmail.com')

You will get the result as NifalNizar1990Hotmailcom

C# SQL Server Connection -Secure & Best

To Create A Highly Secure SqlServer Connection We Must Have Connection String and All The Sql Stuff In Separate Class...
Without Doing Anything Related SQL Stuff  In The Current Class We Can Create A Separate Class For That And We Can Handle From Their...

Create A New Class Call DBConnect and Paste The Following Code
This Class Controls All The Sql Related Stuff

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace MyProject
{
    class DBConnect
    {
        string strconnection= "Server=localhost;Database=dbname;Trusted_Connection=True;";
        SqlConnection sqlcon = new SqlConnection();
        SqlCommand sqlcmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        DataTable dt = new DataTable();

        public void connect()
        {
            sqlcon = new SqlConnection(strconnection);
            sqlcon.Open();
        }
       
        public void disconnect()
        {
            if (sqlcon.State == ConnectionState.Open)
            {
                sqlcon.Close();
                sqlcon.Dispose();
            }
        }
       
        public DataTable ReadData(string query)
        {
            try
            {
                connect();
                sqlcmd = new SqlCommand(query, sqlcon);
                da = new SqlDataAdapter(sqlcmd);
                dt = new DataTable();
                da.Fill(dt);          
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                disconnect();
            }
            return dt;
        }
    
        public void QryCommand(string query)
        {
            try
            {
                connect();
                sqlcmd = new SqlCommand(query, sqlcon);
                sqlcmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                disconnect();
            }
        }
    }
}

We  Can Use Following Codes To Do Changes In The Database


1.Insert Data

string query = "INSERT INTO ur_table VALUES('value1','value2')";
DBConnect dbc = new DBConnect();
dbc.connect();
dbc.QryCommand(query);

2.Get Single Data

string query = "SELECT * FROM ur_table";
DBConnect dbc = new DBConnect();
dbc.connect();
DataTable dt = new DataTable();
dt = dbc.ReadData(query);
foreach (DataRow row in dt.Rows)
{
       txtName.Text = row[0].ToString();
}