How to split a row in one column into two columns

Question

I have a table like this

id          value
-------     ---------------
1           ind.kolkatta
2           ind.pune
3           ind.mumbai
4           pak.lahore
5           pak.karachi
6           uae.sharjah

I want to return the following table:

id          contry       place
-------     ---------    ----------
1           ind          kolkatta
2           ind          pune
3           ind          mumbai
4           pak          lahore
5           pak          karachi
6           uae          sharjah

How can I do this using MSSQL.? I already did in MYSQL using the SUBSTRING_INDEX function

My query is MySql

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(`value`, '.', 1), '.', -1) as contry,
       SUBSTRING_INDEX(SUBSTRING_INDEX(`value`, '.', 2), '.', -1) as place 
FROM   `table`

Answer 1

Try the following query: -

SELECT ID, SUBSTRING(value, 1, CHARINDEX('.', value)-1) AS contry,
SUBSTRING(value, CHARINDEX(',', value)+1, LEN(value)) AS  place
FROM YOUR_TABLE;

This may be useful for you.

Answer 2

Hope the below request helps you.

SELECT  id, SUBSTRING(value,1,3) as Country,SUBSTRING(value,4,LEN(value))AS Place FROM TableName

Answer 3

:

SELECT  Id,
        LEFT(Value, CHARINDEX('.', Value)-1) AS Country, 
        STUFF(Value, 1, CHARINDEX('.', Value), '') AS Place
FROM    Table

King King

SELECT  Id,
        PARSENAME(value, 2) AS Country, 
        PARSENAME(Value, 1) AS Place
FROM    Table

, ( SSMS 2012).

Answer 4

, , .

DECLARE @FILE VARCHAR(55) = 'ind.kol.katta.test1.test2.test3.test4'
DECLARE @FILEFUN AS VARCHAR(55) = LEFT(@FILE,CHARINDEX('.',@FILE))
DECLARE @FILENAMEOUTPUT AS TABLE(Name Varchar(55))

WHILE LEN(@FILE) > 0
BEGIN
INSERT INTO @FILENAMEOUTPUT
SELECT REPLACE(@FILEFUN,'.','')
SET @FILE = REPLACE(@FILE,@FILEFUN,'')
SET @FILEFUN = iif(CHARINDEX('.',@FILE)=0,@FILE,LEFT(@FILE,CHARINDEX('.',@FILE)))
END

SELECT * FROM @FILENAMEOUTPUT