Skip to content
Advertisement

How to find rows in SQL DB with a definite word length?

Good day, I’ve got a column with text in my table, but i need to export words 17 letters long only in it.

I tried to use

where description like '%_%_%_%_%_%_%_%_%_%_%_%_%_%_%'

but my query newer completes.

This is an example of text cell

REIGHT CAR DUMP T.M. “HOWO”, COMMERCIAL NAME A7, MODEL TOTAL 4 PIECES, WITHOUT PACKING IDENTIFICATION NUMBER ENGINE NUMBER 191007018177; TRANSMISSION NUMBER NO; NO BRIDGE NUMBERS 3) VIN: LZZ5DYSD5KA545780; CHASSIS NUMBER LZZ5DYSD5KA545780; ENGINE NUMBER 191007018247; TRANSMISSION NUMBER NO; NO BRIDGE NUMBERS 4) VIN: LZZ5DYSD2KA545784; CHASSIS NUMBER LZZ5DYSD2KA545784; ENGINE NUMBER 191007018137; TRANSMISSION NUMBER NO; BRIDGE ROOMS ARE NOT AVAILABLE FREIGHT VEHICLE –

I am using Microsoft SQL management studio

Advertisement

Answer

If you are using sql server

where LEN(description) = 17 

Edit: I have implemented it in sql server, convert it into ms sql 2012

drop table #asas

create table #asas
(
RN int identity(1,1),
description  varchar(max)
)
insert into #asas values('REIGHT CAR DUMP T.M. "HOWO", 
COMMERCIAL NAME A7, 
MODEL TOTAL 4 PIECES, 
WITHOUT PACKING IDENTIFICATION NUMBER ENGINE NUMBER 191007018177; 
TRANSMISSION NUMBER NO; NO BRIDGE NUMBERS 3) 
VIN: LZZ5DYSD5KA545780; 
CHASSIS NUMBER LZZ5DYSD5KA545780; 
ENGINE NUMBER 191007018247; 
TRANSMISSION NUMBER NO; NO BRIDGE NUMBERS 4) 
VIN: LZZ5DYSD2KA545784; CHASSIS NUMBER LZZ5DYSD2KA545784; ENGINE NUMBER 191007018137; TRANSMISSION NUMBER NO; BRIDGE ROOMS ARE NOT AVAILABLE FREIGHT VEHICLE –
VIN: LZZ5DYSD2KA545700;'),
('sddddddddddddddddddddddddddddddVIN: LZZ5DYSD2KA545700;sddddddddddVIN: LZZ5DYSD2KA543300;oooooooooVIN: LZZ5DYSD2KA544400;iuiuiuiuiuiuVIN: LZZ5DYSD2KA555700;'),
('sssdsdsdsddddddddVIN: LZZ5DYSD2KA545700;sddddd323232dddddVIN: LZZ5DYSD2KA543300;oooooooooVIN: LZZ5DYSD2KA544400;iuiuiuiuiuiuVIN: LZZ5DYSD2KA555700;')

drop table #table
create table #table
(
RN int identity (1,1),
result varchar(max)
)

--drop table #table
declare @first int,@newString varchar(max),@v1  varchar(max),@originalString varchar(max),
@initcount int=1,@count int
select @count=count(*) from #asas



while(@initcount<=@count)
Begin
insert into #table values('')
set @originalString=''
select @originalString=[description] from #asas where RN=@initcount

    while(PATINDEX('%VIN%',@originalString)>0)
    begin

    select @first= PATINDEX('%VIN%',@originalString)

    --select SUBSTRING(@originalString,PATINDEX('%VIN%',@originalString),22) as result 

    select @v1=SUBSTRING(@originalString,PATINDEX('%VIN%',@originalString),22)

    update #table set result=result+', '+@v1 where RN=@initcount

    select @originalString=SUBSTRING(@originalString,PATINDEX('%VIN%',@originalString)+22,len(@originalString))

    end

Set @initcount=@initcount+1
End
select * from #table



User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement