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