RSS

Category Archives: SQL Query

Split string on multiple delimited pass function sql


In This Article , i will write function that  split the string based onmultiple delimiters pass  and also remove the Extra spaces around the string.

--Create this user deinfed function.
 
CREATE FUNCTION dbo.[UDF_Split_String_On_Multiple_Delimiters]
(
      @String VARCHAR(MAX),  -- Variable for string
      @delimiter VARCHAR(50) -- Delimiter in the string
)
RETURNS @Table TABLE(        --Return type of the function
Splitcolumn VARCHAR(MAX)
)
BEGIN
 
        DECLARE @Xml AS XML
        DECLARE @REVISED_STRING VARCHAR(MAX)
 
        ;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
        N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
        N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
        N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
        FROM N3 AS X, N3 AS Y)
 
        SELECT @REVISED_STRING=STUFF((SELECT '' + (Case When
                PATINDEX('%[' + @delimiter + ']%',SUBSTRING(@String,Nums.n,1)) >0
                Then ',' else LTRIM(RTRIM(SUBSTRING(@String,Nums.n,1))) end)
        FROM N4 Nums WHERE Nums.n<=LEN(@String)  FOR XML PATH('')),1,0,'')
 
        SET @Xml = cast(('<a>'+replace(@REVISED_STRING,
                ',','</a><a>')+'</a>') AS XML)
 
        INSERT INTO @Table SELECT A.value('.', 'varchar(max)')
                as [Column] FROM @Xml.nodes('a') AS FN(a)
 
RETURN
END
GO
 
Call Function (One way)
 
 SELECT * FROM dbo.[UDF_Split_String_On_Multiple_Delimiters] (String , multiple delimiter)
SELECT * FROM dbo.[UDF_Split_Based_On_Multiple_Delimiters]
('sajjad,ahmed ; waleed; Ali',',;:')
 
Call Function(Second Way)
 
 SELECT * FROM dbo.[UDF_Split_String_On_Multiple_Delimiters] (String , multiple delimiter)
SELECT * FROM dbo.[UDF_Split_Based_On_Multiple_Delimiters]
('sajjad[}]ahmed [}] waleed[}]Ali','[}]')
GO
 
Advertisements
 
Leave a comment

Posted by on July 23, 2014 in SQL Query

 

Tags:

update specific string of sql column


 

UPDATE [table]
SET [column] = REPLACE([column], 'foo', 'bar')



Example 

UPDATE  [Treasury2].[dbo].[tblBranch]
SET [BranchName] = REPLACE([BranchName], 'SAMA', 'Sajjad Ahmed')


sql column update
 
Leave a comment

Posted by on February 6, 2014 in SQL Query

 

Tags: ,

microsoft sql server error 5120 access denied


Use the below script to find the sql service account

 

declare @sqlser varchar(20)

EXEC master..xp_regread @rootkey=‘HKEY_LOCAL_MACHINE’,@key=‘SYSTEM\CurrentControlSet\Services\MSSQLSERVER’,

@value_name=‘objectname’, @value=@sqlser OUTPUT

PRINT ‘Account Starting SQL Server Service:’ +convert(varchar(30),@sqlser)

After getting the service account try the below steps to provide privilege

 

  • Right click on the folder where your database file like (Testdb.mdf & Test.ldf) and click on properties
  • Click on security tab
  • Click on Add button and add sql service account
  • Provide modify privilege and click ok
  • Verify both mdf and ldf have modify privilege
  • Give full control againt your system login user
  • Attach the db!
 
Leave a comment

Posted by on November 14, 2013 in SQL Query

 

Tags: , , , ,

remove specific character from column sql


if you want to remove specific character string from string .

like

~/UpLoadedImages/Product20081415160554.jpg

you want to remove ” ~/UpLoadedImages/” this part of string then use this query for that .

UPDATE tblImages
SET vchPath = replace(vchPath, ‘~/UpLoadedImages/’, ”)

 

 

 
Leave a comment

Posted by on August 15, 2013 in SQL Query

 

remove html tags sql server


One of the developer at my company asked is it possible to parse HTML and retrieve only TEXT from it without using regular expression. He wanted to remove everything between < and > and keep only Text. I found question very interesting and quickly wrote UDF which does not use regular expression.

Following UDF takes input as HTML and returns TEXT only. If there is any single quotes in HTML they should be replaced with two single quotes (not double quote) before it is passed as input to function.

CREATE FUNCTION [dbo].[udf_StripHTML]
(@HTMLText VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Length INT
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
WHILE @Start > 0
AND @End > 0
AND @Length > 0
BEGIN
SET @HTMLText = STUFF(@HTMLText,@Start,@Length,'')
SET @Start = CHARINDEX('<',@HTMLText)
SET @End = CHARINDEX('>',@HTMLText,CHARINDEX('<',@HTMLText))
SET @Length = (@End - @Start) + 1
END
RETURN LTRIM(RTRIM(@HTMLText))
END
GO

 

 

est above function like this :

SELECT dbo.udf_StripHTML('<b>UDF at humrahimcs </b><br><br><a href="http://www.https://humrahimcs.wordpress.com">https://humrahimcs.wordpress.com</a&gt;')

Result Set:

UDF at humrahimcs

 

select
substring ([dbo].[Team].Description,0,50) ,
substring (dbo.udf_StripHTML([dbo].[Team].Description),0,50)
from
[dbo].[Team]

 
Leave a comment

Posted by on June 3, 2013 in SQL Query

 

Tags: ,

Convert Multiple Rows into One Comma Separated Values in SQL server 2008


 

I have come across a problem while doing development. Finally I have sorted this problem. So I have decided to write an blog on this. The problem and solution is as below:-

Requirment

I have table as given below

 comma sep

Now I want to convert state row values to one string value.

Ex: Andhra Pradesh, Arunachal Pradesh, Assam, Maharashtra

Solutions

For the above problem, below are different queries to achieve this.

Query 1: Using “COALESCE” function

DECLARE @temp VARCHAR(MAX)
SELECT
 @temp = COALESCE(@temp+’, ‘ ,”) + title
FROM
 [dbo].[country]
SELECT
 @temp ‘title’

Query 2: Using without “COALESCE” function

DECLARE @temp VARCHAR(MAX)
SET
 @temp = 
SELECT
 @temp = @temp + title + ‘, ‘
FROM
 [dbo].[country]
SELECT
 SUBSTRING(@temp, 0, LEN(@temp))

Query 3: Using “FOR XML PATH”

DECLARE @temp VARCHAR(MAX)
SET
 @temp = (SELECT ‘, ‘ + cast(s.title as varchar)
FROM [dbo].[country] 
s
ORDER BY s.title

FOR XML PATH(”
))
SELECT
 SUBSTRING(@temp, 2, 200000) AS title

Hope this will help you to solve your problem.

 
Leave a comment

Posted by on May 24, 2013 in SQL Query

 

Tags: