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!!
-
Have you considered doing that extraction of part of your data with whatever high level language you are using for your application.Dragonthoughts– Dragonthoughts2018-06-11 09:28:31 +00:00Commented Jun 11, 2018 at 9:28
Add a comment
|
4 Answers
You can use parsename() function :
select *, parsename(replace(col, '-', '.'), 3) as newcol
from table t;
2 Comments
Juozas
Thanks for idea. Cool. I think, Its best answer and recommend use this. Here is real example of usage: pastebin.com/FZ3u6nHs
EzLo
Keep in mind that this will work if you have 3 hyphens (4 strings concatenated) only.
SELECT SUBSTR("Delhi-123456-PO-2356",7,6) AS answer
Explaination: function SUBSTR(data,startIndex,length)
startIndex calculated from 0
1 Comment
uday adidham
may delhi will be long string in future.
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];
Comments
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.
