Solved

data extract

Posted on 2014-04-02
8
242 Views
Last Modified: 2014-05-02
<B>1979 date sale.</B></I> NGC Census: (398/1885). PCGS Population (545/1407).

Hi experts, above is a sample row from one of the column, I would need data between the two <B> 's, please help extract the data,
0
Comment
Question by:sqlcurious
8 Comments
 

Author Comment

by:sqlcurious
ID: 39972631
for example: I need  this string to be extracted from the above string :

1979 date sale.
0
 
LVL 22

Expert Comment

by:plusone3055
ID: 39972656
SELECT
    ColumnNameHere,
 SUBSTRING(ColumnNameHere,
        ISNULL(2 + LEN(ColumnNameHere) - NULLIF(CHARINDEX('<B>', REVERSE(ColumnNameHere)), 0), 0),
        CASE CHARINDEX('</B>', ColumnNameHere) WHEN 0 THEN LEN(ColumnNameHere) + 1 ELSE CHARINDEX('</B>', ColumnNameHere) END -
        ISNULL(2 + LEN(ColumnNameHere) - NULLIF(CHARINDEX('<B>', REVERSE(ColumnNameHere)), 0), 0)) AS Comparison
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39972672
Here is a working example

declare @x nvarchar(100)
select @x = '<B>1979 date sale.</B></I> NGC Census: (398/1885). PCGS Population (545/1407).'

select replace(left(@x, CHARINDEX('</B>', @x) -1), '<B>', '')

Open in new window


Here is an example from a table

select replace(left(myColumn, CHARINDEX('</B>', myColumn) -1), '<B>', '') from myTable

Open in new window

0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:sqlcurious
ID: 39972775
Hi Lee, thanks for the example, it says 'Invalid length parameter passed to the LEFT or SUBSTRING function', what could be the reason?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 39972936
try this.
select CASE WHEN @x LIKE '%<B>%</B>%' 
            THEN SUBSTRING(@x, CHARINDEX('<B>',@x)+3,CHARINDEX('</B>',@x)-CHARINDEX('<B>',@x)-3) END

Open in new window

declare @x nvarchar(100)
select @x = '<A>other</A><B>1979 date sale.</B></I> NGC Census: (398/1885). PCGS Population (545/1407).'

select CASE WHEN @x LIKE '%<B>%</B>%' 
            THEN SUBSTRING(@x, CHARINDEX('<B>',@x)+3,CHARINDEX('</B>',@x)-CHARINDEX('<B>',@x)-3) END
-- 1979 date sale.

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39973198
You probably have some null values in the column. Put a where clause in there to filter nulls
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39973598
Here's full code, including showing the original data and providing sample data.

It shows blank if no <B> entry is found; if <B> is found but no </B> entry is found, it will treat the rest of the line as <B> data:


SELECT
    row_data,
    CASE WHEN b_data_start = 0 OR b_data_start > b_data_end THEN ''
        ELSE SUBSTRING(row_data, b_data_start + 3, b_data_end - b_data_start - 3) END AS b_data
FROM (
    SELECT '<B>1979 date sale.</B></I> NGC Census: (398/1885). PCGS Population (545/1407). ' AS row_data UNION ALL
    SELECT '<A>...asd;lkj;ldkljf;lkj  ,</A><B>1981 sale date ... blah blah to make this a longer entry</B></I> NGC Census: (398/1885). PCGS Population (545/1407). ' UNION ALL
    SELECT '</I> NGC Census: (398/1885). PCGS Population (545/1407). ' UNION ALL
    SELECT '</I> NGC Census: (398/1885). PCGS Population (545/1407). <B> b entry w/o closing token'
) AS sample_data
CROSS APPLY (
    SELECT CHARINDEX('<B>', row_data) AS b_data_start, CHARINDEX('</B>', row_data) AS b_data_end_prelim
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN b_data_start > 0 AND b_data_end_prelim = 0 THEN LEN(row_data) + 3 ELSE b_data_end_prelim END AS b_data_end
) AS ca2




Stripped-down code just to extract b-value from existing table:

SELECT
    CASE WHEN b_data_start = 0 OR b_data_start > b_data_end THEN ''
        ELSE SUBSTRING(row_data, b_data_start + 3, b_data_end - b_data_start - 3) END AS b_data
FROM dbo.your_table_name
CROSS APPLY (
    SELECT CHARINDEX('<B>', row_data) AS b_data_start, CHARINDEX('</B>', row_data) AS b_data_end_prelim
) AS ca1
CROSS APPLY (
    SELECT CASE WHEN b_data_start > 0 AND b_data_end_prelim = 0 THEN LEN(row_data) + 3 ELSE b_data_end_prelim END AS b_data_end
) AS ca2
0
 

Author Closing Comment

by:sqlcurious
ID: 40038142
thanks
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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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