Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

data extract

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

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

792 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