Click here to Skip to main content
14,732,830 members
Please Sign up or sign in to vote.
1.31/5 (4 votes)
See more:
In SOL Server, I get the value for the parameter, @codeName as "1023 - Hydrabad" from ASP.NET

ie, @codeName = "1023 - Hydrabad"

I want Hydrabad alone. How to split it?
Posted
Updated 16-Mar-20 9:19am

CREATE FUNCTION [dbo].[fnSplitString] 
( 
  @string VARCHAR(MAX), 
  @delimiter VARCHAR(MAX) 
) 
RETURNS @output TABLE(splitdata VARCHAR(MAX)) 
BEGIN 

  DECLARE @Xml XML

  SET @Xml = CAST(('<a>'+REPLACE(@string,@delimiter,'</a><a>')+'</a>') AS XML)

  INSERT INTO @output (splitdata)
    SELECT ltrim(rtrim(A.value('.', 'VARCHAR(MAX)')))  FROM @Xml.nodes('a') AS FN(a)

  RETURN 
END


Arbitrary length delimiter
   
first u create function

ALTER FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
WITH Split(stpos,endpos) 
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
 
--then u can use this function like this 
 
select * from  dbo.Split(string,'-') where id=1
   
v3
I think the best solution for such cases is to use Regular Expressions. Check below code project article.


[^]
   
string str = "1023 - Hydrabad";
string[] strArray = str.Split('-');
   
Dim paramval As String = "1023 - Hydrabad"
        Dim paramvalue() As String = paramval.Split("-")
       @codeName = paramvalue(1).ToString()
   
DECLARE @LongSentence VARCHAR(MAX)
 DECLARE @FindSubString VARCHAR(MAX)
 SET @LongSentence = 'SQL,SERVER,TUTORIAL'
 SET @FindSubString = ','
 SELECT (LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, ''))) CntReplacedChars,
 (LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceChars




this is working fine for me
   
You can use SQL Server's SUBSTRING and CHARINDEX functions:
DECLARE @codeName VarChar(100)
SET @codeName = '1023 - Hydrabad'

SELECT SUBSTRING(@codeName, CHARINDEX('-', @codeName) + 2, 100)

or use appropriate similar .NET methods in your code behind.
   
Comments
Jenas929 7-May-19 23:29pm
   
How to save the 'Hydrabad' to another variable?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900