Solved

split string value in sql server data

Posted on 2009-05-10
5
345 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

813 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

10 Experts available now in Live!

Get 1:1 Help Now