Solved

data extract

Posted on 2014-04-02
8
240 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
 

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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:
ScottPletcher 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

757 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

18 Experts available now in Live!

Get 1:1 Help Now