Click here to Skip to main content
14,691,375 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

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?
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
   
Dim paramval As String = "1023 - Hydrabad"
        Dim paramvalue() As String = paramval.Split("-")
       @codeName = paramvalue(1).ToString()
   
string str = "1023 - Hydrabad";
string[] strArray = str.Split('-');
   
I think the best solution for such cases is to use Regular Expressions. Check below code project article.


[^]
   
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
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
   

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