Solved

# data extract

Posted on 2014-04-02
246 Views
<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
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

Author Comment

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

1979 date sale.
0

LVL 22

Expert Comment

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

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>', '')
``````

Here is an example from a table

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

Author Comment

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

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
``````
``````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.
``````
0

LVL 25

Expert Comment

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

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

ID: 40038142
thanks
0

## Featured Post

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…
###### Suggested Courses
Course of the Month6 days, 13 hours left to enroll

#### 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.