Solved

SQL commnad

Posted on 2009-05-20
1
175 Views
Last Modified: 2012-05-07
HI,

I have column in sql server 2005 which contain the following format of data.

01.20.30.16
02.30.60.189 etc.

Four parts of digits seperated by a period. How can I select each part seperate?

ayha
0
Comment
Question by:ayha1999
1 Comment
 
LVL 42

Accepted Solution

by:
pcelba earned 30 total points
ID: 24433313
The following code does it for you, you just have to provide column name
SELECT LEFT(YourCol, CHARINDEX('.', YourCol)-1) as part1,
       SUBSTRING(YourCol, CHARINDEX('.',YourCol)+1, CHARINDEX('.',YourCol,CHARINDEX('.',YourCol)+1)-CHARINDEX('.',YourCol)-1) as Part2,
       REVERSE(SUBSTRING(REVERSE(YourCol), CHARINDEX('.',REVERSE(YourCol))+1, CHARINDEX('.',REVERSE(YourCol),CHARINDEX('.',REVERSE(YourCol))+1)-CHARINDEX('.',REVERSE(YourCol))-1)) as Part3,
       RIGHT(RTRIM(YourCol), CHARINDEX('.',REVERSE(RTRIM(YourCol)))-1) as Part4
FROM YourTable

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Backup skipping a few tables 7 52
Query to Add Late Tolerance 10 76
convert null in sql server 12 48
Parse this column 6 27
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question