14,732,830 members
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

## Solution 8

```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

## Solution 7

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

## Solution 5

I think the best solution for such cases is to use Regular Expressions. Check below code project article.

[^]

## Solution 4

string str = "1023 - Hydrabad";
string[] strArray = str.Split('-');

## Solution 3

```Dim paramval As String = "1023 - Hydrabad"
Dim paramvalue() As String = paramval.Split("-")
@codeName = paramvalue(1).ToString()```

## Solution 2

```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

## Solution 1

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.
Jenas929 7-May-19 23:29pm

How to save the 'Hydrabad' to another variable?