In this post, I will show you how to Convert String To Color in SQL Server. The following code will describe how to convert String To Color In SQL Server 2016. And now write code below:

CREATE FUNCTION dbo.fn_conversion_string_color(  

@in_string VARCHAR(200)  

)  

RETURNS NVARCHAR(500)  

AS  

BEGIN  

DECLARE @fsetprefix BIT, -- append '0x' to the output   

@pbinin VARBINARY(MAX), -- input binary stream   

@startoffset INT, -- starting offset    

@cbytesin INT, -- length of input to consider, 0 means total length   

@pstrout NVARCHAR(MAX),   

@i INT,   

@firstnibble INT ,  

@secondnibble INT,   

@tempint INT,  

@hexstring CHAR(16)  

SELECT @fsetprefix = 1,  

@pbinin = SUBSTRING(HASHBYTES('SHA1', @in_string), 1, 3),  

@startoffset = 1,  

@cbytesin = 0   

-- initialize and validate   

IF (@pbinin IS NOT NULL)   

BEGIN    

SELECT @i = 0,   

@cbytesin = CASE  WHEN (@cbytesin > 0 AND @cbytesin <= DATALENGTH(@pbinin))  

  THEN @cbytesin  

  ELSE DATALENGTH(@pbinin)  

  END,   

@pstrout =  CASE  WHEN (@fsetprefix = 1)  

  THEN N'0x'  

  ELSE N''  

  END,   

@hexstring = '0123456789abcdef'   

--the output limit for nvarchar(max) is 2147483648 (2^31) bytes, that is 1073741824 (2^30) unicode characters   

IF (  

((@cbytesin * 2) + 2 > 1073741824)  

OR ((@cbytesin * 2) + 2 < 1)  

OR (@cbytesin IS NULL )  

)   

RETURN NULL   

IF (  

( @startoffset > DATALENGTH(@pbinin) )  

OR (@startoffset < 1 )  

OR (@startoffset IS NULL )  

)   

RETURN NULL   

-- adjust the length to process based on start offset and total length   

IF ((DATALENGTH(@pbinin) - @startoffset + 1) < @cbytesin)   

SELECT @cbytesin = DATALENGTH(@pbinin) - @startoffset + 1   

-- do for each byte   

WHILE (@i < @cbytesin)   

BEGIN   

-- Each byte has two nibbles  which we convert to character   

SELECT @tempint = CAST(SUBSTRING(@pbinin, @i + @startoffset, 1) AS INT)   

SELECT @firstnibble = @tempint / 16   

SELECT @secondnibble = @tempint % 16   

-- we need to do an explicit cast with substring for proper string conversion.     

SELECT @pstrout = @pstrout +   

  CAST(SUBSTRING(@hexstring, (@firstnibble+1), 1) AS NVARCHAR) +   

  CAST(SUBSTRING(@hexstring, (@secondnibble+1), 1) AS NVARCHAR)   

SELECT @i = @i + 1   

END   

END   

RETURN  '#' + UPPER(RIGHT(@pstrout, 6))  

 END  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

OnceSQL Server Full-Text Search with rank values  I wrote a post titled enabling Fulltext search in Azure SQL database discussing Full-Text search in Azure. while using it with one of my databases, needed to show the result of the search ordered by however well they match to the search criteria. in order to sort the result as i need, the best is, get a rank generated for every row and use it for ordering the result. I had used Freetext operate for obtaining the result but if i realized that this can not be achieved using the Freetext function.

The CONTAINSTABLE and FREETEXTTABLE functions return a column named Rank for showing the rank related to the record based on matching. this can be used get the result sorted based on it, showing most relevant records at the top. Remember, the higher value of the Rank generated indicates the best matching.

Now, write the following code:
view plainprint?

    -- Creating a table  
    CREATE TABLE dbo.EmployeeDetails  
    (  
     EmployeeDetailsId int identity(1,1) not null  
     , constraint pk_EmployeeDetails primary key (EmployeeDetailsId)  
     , WorkingExperience nvarchar(4000) not null  
     , ProjectsWorked nvarchar(4000) not null  
     , Resume nvarchar(max)   
    )  
    GO  
      
    CREATE FULLTEXT CATALOG EmployeeCatelog;  
    GO  
      
    CREATE FULLTEXT INDEX ON dbo.EmployeeDetails   
     (WorkingExperience, ProjectsWorked, Resume) KEY INDEX pk_EmployeeDetails  
     ON EmployeeCatelog;  
     -- By default CHANGE_TRACKING = AUTO  
      
      
    -- Once enabled, search can be performed;  
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((WorkingExperience, ProjectsWorked, Resume), 'SQL');  
      
    SELECT *  
    FROM dbo.EmployeeDetails  
    WHERE freetext ((Resume), 'SQL');  
      
    -- Get the rank and sort the result using it  
    SELECT t.Rank, e.*  
    FROM dbo.EmployeeDetails e  
     INNER JOIN CONTAINSTABLE (dbo.EmployeeDetails, (WorkingExperience, ProjectsWorked, Resume), 'SQL') AS t  
      ON e.EmployeeDetailsId = t.[Key]  
    ORDER BY t.Rank DESC  

HostForLIFE.eu SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.

In This code snippet, i will tell you about how Uploading Excelsheet Data in SQL Server Table. Now, write the followind code:

protected void btnSend_Click(object sender, EventArgs e) { 
    try { 
        string path = string.Concat(Server.MapPath("~/File/" + fileuploadExcel.FileName)); 
        fileuploadExcel.SaveAs(path); 
        string connExcelString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", path); 
        OleDbConnection excelConnection = new OleDbConnection(connExcelString); 
        OleDbCommand cmd = new OleDbCommand("Select [Name],[Mobile],[Email],[City],[DataId],[Date],[Source] from [Sheet1$]", excelConnection); 
        excelConnection.Open(); 
        OleDbDataReader dReader; 
        dReader = cmd.ExecuteReader(); 
        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection); 
        SqlBulkCopyColumnMapping mapName = new SqlBulkCopyColumnMapping("Name", "Name"); 
        sqlBulk.ColumnMappings.Add(mapName); 
        SqlBulkCopyColumnMapping mapMobile = new SqlBulkCopyColumnMapping("Mobile", "Mobile"); 
        sqlBulk.ColumnMappings.Add(mapMobile); 
        SqlBulkCopyColumnMapping mapEmail = new SqlBulkCopyColumnMapping("Email", "Email"); 
        sqlBulk.ColumnMappings.Add(mapEmail); 
        SqlBulkCopyColumnMapping mapCity = new SqlBulkCopyColumnMapping("City", "City"); 
        sqlBulk.ColumnMappings.Add(mapCity); 
        //SqlBulkCopyColumnMapping mapState = new SqlBulkCopyColumnMapping("State", "State"); 
        //sqlBulk.ColumnMappings.Add(mapState); 
        SqlBulkCopyColumnMapping mapDataId = new SqlBulkCopyColumnMapping("DataId", "DataId"); 
        sqlBulk.ColumnMappings.Add(mapDataId); 
        SqlBulkCopyColumnMapping mapAmount = new SqlBulkCopyColumnMapping("Date", "Date"); 
        sqlBulk.ColumnMappings.Add(mapAmount); 
        SqlBulkCopyColumnMapping mapSource = new SqlBulkCopyColumnMapping("Source", "Source"); 
        sqlBulk.ColumnMappings.Add(mapSource); 
        //Give your Destination table name 
        sqlBulk.DestinationTableName = "UploadedExcelData"; 
        sqlBulk.WriteToServer(dReader); 
        excelConnection.Close(); 
        UpdateRecords(); 
        lblMsg.Text = "File Data Uploaded Successfully... "; 
        File.Delete(path); 
    } catch (Exception ex) { 
        lblMsg.Text = "Something Went Wrong... Plz Check Excel File "; 
        //string script = "<script>alert('" + ex.Message + "');</script>"; 
    } 
}

HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.