Solved

split string value in sql server data

Posted on 2009-05-10
5
343 Views
Last Modified: 2012-05-06
I am using SQL server 2005 database. In one of my table a field will have the values in the following format.
abcdef<ghijk><lmno>pqrs<tuvw> (or)
abc<defghij>klmnop (or)
abcdefghijkl<mnop>

now i want to write a query to select the first part that is, characters before the first '<'
how to do it?
0
Comment
Question by:siva_gk
5 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 24347501
0
 
LVL 1

Accepted Solution

by:
Mikhail Peterburgskiy earned 50 total points
ID: 24347543
declare @str as varchar(max)
select @str = 'abcdef<ghijk><lmno>pqrs<tuvw>'

select LEFT(@str, CHARINDEX('<', @str, 1) - 1)
0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24347715
Use the following query for non-stored procedure approach:

SELECT LEFT(FieldName, CHARINDEX('<', FieldName, 1) - 1) AS ColumnName FROM TableName
0
 
LVL 1

Expert Comment

by:rbodepudi
ID: 24348089
It's easy, You can use a combination of functions to acheive this: First you have to find the position of first appearance of '<', then use that in LEFT() to get the desired result.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24348354
well, all you want to locate the position of >, you can do it with quite a few different string function in SQL server. here are two function for you to learn. have a look at below example snippet.


declare @val varchar(max)
set @val='abcdefpqrs'

select LEFT(@val, CHARINDEX('<', @val, 1) - 1)

select substring(@val,1,CHARINDEX('<', @val, 1) - 1)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now