CODEWITHSUNDEEP
sqlsql-server
uday adidham
uday adidham

Reputation: 29

How to get the value from string in Sql Server,

How to get the value from string in Sql Server,

Example : i have one column data like "Delhi-123456-PO-2356". i want to get the only 123456 (after first -) from this string using Sql server.

Without using Stored procedure!!

Upvotes: 1

Views: 68

Answers (4)

Zorkolot
Zorkolot

Reputation: 2017

This should get the correct value no matter the number of hyphens.

DECLARE @teststring varchar(100) = 'Delhi-123456-PO-2356'

SELECT LEFT(SUBSTRING(@teststring, CHARINDEX('-', @teststring) + 1, LEN(@teststring)), 
            CHARINDEX( '-', SUBSTRING(@teststring
                                    , CHARINDEX('-', @teststring) + 1
                                    , LEN(@teststring))) - 1)

Returns

123456

Best practice, would save each of the 4 parts into 4 separate columns. Saving multiple values into a single string is a violation of First Normal Form.

Upvotes: 0

Juozas
Juozas

Reputation: 935

Please use this primitive solution:

declare @str nvarchar(256) = N'Delhi-123456-PO-2356';

select left(
     substring(@str, charindex('-', @str) + 1, len(@str) - charindex('-', @str))
    ,charindex('-', substring(@str, charindex('-', @str) + 1, len(@str) - charindex('-', @str))) - 1
);

Or more advanced:

declare @str nvarchar(256) = N'Delhi-123456-PO-2356';

;with [data] as (
    select [str] =  substring(@str, charindex('-', @str) + 1, len(@str) - charindex('-', @str))
)
select left(
     [str]
    ,charindex('-', [str]) - 1
) from [data];

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use parsename() function :

select *, parsename(replace(col, '-', '.'), 3) as newcol
from table t;

Upvotes: 2

manojsingh
manojsingh

Reputation: 61

SELECT SUBSTR("Delhi-123456-PO-2356",7,6) AS answer

Explaination: function SUBSTR(data,startIndex,length)

startIndex calculated from 0

Upvotes: 0

Related Questions