sqlcurious
asked on
data extract
<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,
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,
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
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
Here is a working example
Here is an example from a table
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
ASKER
Hi Lee, thanks for the example, it says 'Invalid length parameter passed to the LEFT or SUBSTRING function', what could be the reason?
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.
You probably have some null values in the column. Put a where clause in there to filter nulls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
ASKER
1979 date sale.