• Status: Solved
• Priority: Medium
• Security: Public
• Views: 430

# splitting a string

i am trying to convert a field containing a string into 3 fields.

the string is of the format

alphanumeric decimal [comma] decimal

eg.

Moyo Island -8.259857,117.573064

i want it split in 3 eg.

Moyo Island    |   -8.259857   |   117.573064
0
tommarshallandrews
2 Solutions

Commented:
0

Commented:

Look at the section
3. Parse the list into a set
0

progCommented:
that's awkward, your list is not delimited - you could really have done with a delimiting character between the place name and the co-ordinates.  The delimiter mustn't appear in the place name, in this case you have a space which can also appear in the place name.

A function reading back through the string could be written to strip out the two numbers.

0

Author Commented:
Thanks guys.

My idea was to try and strip all the numbers & commas out to get the place name then strip the characters to get a comma separated string and try and pot that into 2 columns.

Would that make sense? If so any idea how??

Thanks
0

Commented:
try it this way
``````declare @temp varchar(max)
declare @var1 varchar(max), @var2 varchar(50), @var3 varchar(50)

set @temp = 'Moyo Island -8.259857,117.573064'

set @temp = REVERSE(@temp)

set @var1 = REVERSE(SUBSTRING(@temp, 1, CHARINDEX(',', @temp, 1)-1))

set @temp = SUBSTRING(@temp, LEN(@var1), LEN(@Temp) - LEN(@var1) +1)
set @var2 = REVERSE(SUBSTRING(@temp, 1, CHARINDEX(' ', @temp, 1)-1))
set @var3  = REVERSE(SUBSTRING(@temp, LEN(@var2), LEN(@Temp) - LEN(@var2) +1))

select @var1, @var2, @var3
``````
0

Commented:
This will do it.

DECLARE @T1 varchar(200) = 'Moyo Island -8.259857,117.573064'

SELECT REVERSE(SUBSTRING(REVERSE(@T1),0, CHARINDEX(',',REVERSE(@T1),0))),
REVERSE(SUBSTRING(REVERSE(@T1),CHARINDEX(',',REVERSE(@T1),0)+1,LEN(@T1)-CHARINDEX(' ',REVERSE(@T1),0)-2)),
REVERSE(SUBSTRING(REVERSE(@T1),CHARINDEX(' ',REVERSE(@T1),0),255))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.