Solved

data extract

Posted on 2014-04-02
8
246 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
[X]
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
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 41

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2016 data tools breakdown.. 1 36
When to use a Unique Index? A Clustered Index? 5 76
Help with SQL pivot 11 49
SQL Server how to create a DYNAMIC TABLE? 11 51
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

732 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