Mysql search with ignore special characters -

-

i have table 'products' has column partnumber.

i want ignore special characters record while searching.

suppose have following 5 records in partnumber: 

xp-12345 mv-334-3454 xp1-5555 vx-ap-xp-1000  vt1232223

now, if try search "xp1", output should come following records

xp-12345 xp1-5555 vx-ap-xp-1000

how write mysql query ?

define mysql function strips symbols provided string.

delimiter // create function strip_symbols(input varchar(255)) returns varchar(255) deterministic no sql begin     declare output varchar(255) default '';     declare c char(1);     declare int default 1;     while < length(input)         set c = substring(input, i, 1);         if c regexp '[a-za-z0-9]'             set output = concat(output, c);         end if;         set = + 1;     end while;     return output; end// delimiter ;

then select records table partnumber symbols stripped contains xp1:

select * products strip_symbols(partnumber) '%xp1%'; -- returns: xp-12345, xp1-5555, vx-ap-xp-1000

this might painfully slow if table large. in case, generated columns (if have mysql 5.7.6 or higher) or creating trigger (if earlier version) keep column in table updated partnumber symbols stripped.


Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more