How to extract partial string based on characters in SQL server?

Question

I have a string field in SQL Server that contains a few items I want to break out into separate fields. Two of them contain double quotes. Here is an example of what exists in the field. This is all in a single string

Example: 111668999 555444888 "3 LOW" "5 HIGH"

What I would like to do is break out the "3 LOW" and "5 HIGH" into their own new fields, but keep the quotes around them. I have successfully separated the numerical values proceeding them, so I am just working on the "3 LOW" "5 HIGH" values. I'm sure it can be done with a combination of CHARINDEX and SUBSTRING but I was struggling with identifying the quotes as start and end points and making sure they remain included in the new fields.

Thanks!

Nolan Shang · Accepted Answer · 2017-08-16 23:11:00Z

2

;with tb(s) as (
   select '111668999 555444888 "3 LOW" "5 HIGH"'
)
select node from tb
cross apply(values(convert(XML,'<n>'+replace(tb.s,'"','</n><n>')+'</n>'))) as c(x)
cross apply( select x.node.value('.','varchar(20)') as node from c.x.nodes('n') x(node)  )as p 
where  patindex('[0-9] [a-Z]%', p.node)>0

    node
1   3 LOW
2   5 HIGH

answered Aug 16, 2017 at 23:11

Nolan Shang

2,3281 gold badge14 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Victor Hugo Terceros · Accepted Answer · 2017-08-16 23:07:35Z

You can create this function

CREATE FUNCTION [dbo].[SplitString]
(
@sString nvarchar(2048),
@cDelimiter nchar(3),
@mark nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
if @sString is null return
declare @iStart int,
        @iPos int
if substring( @sString, 1, 1 ) = @cDelimiter 
begin
    set @iStart = 2
    insert into @tParts
    values( null )
end
else 
    set @iStart = 1
while 1=1
begin
    set @iPos = charindex( @cDelimiter, @sString, @iStart )
    if @iPos = 0
        set @iPos = len( @sString )+1
    if @iPos - @iStart > 0          
        insert into @tParts
        values  ( replace(ltrim(substring( @sString, @iStart, @iPos-        
    @iStart )) + @mark,'""','"'))       
    set @iStart = @iPos+1
    if @iStart > len( @sString ) 
        break
  end
  RETURN

END

GO

And use it this way

SELECT * FROM [dbo].[SplitString] 
('"3 LOW" "5 HIGH" "7 HIGH" "8 HIGH"','" "', '"')

result

"3 LOW"
"5 HIGH"
"7 HIGH"
"8 HIGH"

John Cappelletti · Accepted Answer · 2017-08-16 23:13:55Z

1

This will put the values in fields not rows

Example

Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values
(1,'111668999 555444888 "3 LOW" "5 HIGH"')


Select A.ID
      ,B.*
 From @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = '"'+ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = '"'+ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select replace(A.SomeCol,' "','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

answered Aug 16, 2017 at 23:13

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Collectives™ on Stack Overflow

How to extract partial string based on characters in SQL server?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related