x

Extracting data in SQL 2005

I have a string in one address field. I just want all the data to the left of the FIRST '|' symbol.

Ex: HB Prod, LLC aax@gmail.com 123 Main Avenue New Orleans, LA 70115 US ,ph: 999 999-9999| abcn Brothers Productions, LLC Harlan abc@gmail.com 1111 Avenue New Orleans, LA 70115 US ,ph: 504 891-9999 ,fax: 504 931-9999,Tim MD

more ▼

asked Feb 22, 2011 at 11:00 AM in Default

avatar image

tlredd68
111 7 8 10

What if this data is in a field and I want to extract the first part into a new field. The new field will only contain "HB Prod, LLC aax@gmail.com 123 Main Avenue New Orleans, LA 70115 US ,ph: 999 999-9999"

Feb 22, 2011 at 12:15 PM tlredd68

That's the second query in my answer. If you create a tally table like the one in the link and give this a run in a test environment, you'll be able to play around with the results. As the query stands, when you run it, you'll get

HB Prod, LLC aax@gmail.com 123 Main Avenue New Orleans, LA 70115 US ,ph: 999 999-9999

HB Prod 2, LLC aax@gmail.com 123 Main Avenue New Orleans, LA 70115 US ,ph: 999 999-9999

because I created two rows (to show that you can use multiple rows with one tally query).

Feb 22, 2011 at 12:38 PM Kevin Feasel
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Use a tally table.

If you create a tally table, you can do this pretty easily:

     declare @Parameter varchar(max);
     set @Parameter = '|' + 'HB Prod, LLC aax@gmail.com 123 Main Avenue New Orleans, LA 70115 US ,ph: 999 999-9999| abcn Brothers Productions, LLC Harlan abc@gmail.com 1111 Avenue New Orleans, LA 70115 US ,ph: 504 891-9999 ,fax: 504 931-9999,Tim MD ' + '|';
     
     SELECT TOP 1
         SUBSTRING(@Parameter,N+1,CHARINDEX('|',@Parameter,N+1)-N-1)
     FROM 
         dbo.sp_tally
     WHERE 
         N < LEN(@Parameter)
         AND SUBSTRING(@Parameter,N,1) = '|'
 
 

If you have rows with this data instead of just a variable, it's still an easy job for a tally table. You just have to precede and end the string with a pipe.

     create table #Parm
 (
     UglyString varchar(max)
 )
 
 insert into #Parm values('HB Prod, LLC aax@gmail.com 123 Main Avenue New Orleans, LA 70115 US ,ph: 999 999-9999| abcn Brothers Productions, LLC Harlan abc@gmail.com 1111 Avenue New Orleans, LA 70115 US ,ph: 504 891-9999 ,fax: 504 931-9999,Tim MD ')
 insert into #Parm values('HB Prod 2, LLC aax@gmail.com 123 Main Avenue New Orleans, LA 70115 US ,ph: 999 999-9999| abcn Brothers Productions, LLC Harlan abc@gmail.com 1111 Avenue New Orleans, LA 70115 US ,ph: 504 891-9999 ,fax: 504 931-9999,Tim MD ')
 
 SELECT 
     N,
     SUBSTRING('|' + p.UglyString + '|', N+1, CHARINDEX('|', '|' + p.UglyString + '|', N+1 ) - N-1)
 FROM 
     dbo.sp_tally t
     cross join #Parm p
 WHERE 
     N = 1
     AND N < LEN('|' + p.UglyString + '|')
     AND SUBSTRING('|' + p.UglyString + '|',N,1) = '|'
 
 drop table #Parm

In this second case, I created two rows to show that you can have multiple rows worth of data with this solution.

more ▼

answered Feb 22, 2011 at 11:20 AM

avatar image

Kevin Feasel
6.2k 4 7 15

(comments are locked)
10|1200 characters needed characters left

I figured it out. Select LEFT(fieldname,CHARINDEX('|',fieldname,1)) From TableName

more ▼

answered Feb 22, 2011 at 01:06 PM

avatar image

tlredd68
111 7 8 10

(comments are locked)
10|1200 characters needed characters left

Select LEFT(table Field Name,CHARINDEX('|',table Field Name,1)) From Table Name

more ▼

answered Feb 22, 2011 at 10:50 PM

avatar image

MCASumit
63 3 3 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2031
x1155

asked: Feb 22, 2011 at 11:00 AM

Seen: 1343 times

Last Updated: Feb 22, 2011 at 11:00 AM

Copyright 2017 Redgate Software. Privacy Policy