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

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
Asked:
tommarshallandrews
2 Solutions
 
Ephraim WangoyaCommented:
0
 
Ephraim WangoyaCommented:

Look at the section
3. Parse the list into a set
0
 
deightonprogCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tommarshallandrewsAuthor 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
 
Ephraim WangoyaCommented:
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

Open in new window

0
 
lluddenCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now