How to remove values that contain 'text + numbers' from column in SQL












0















IN MySQL, I'm trying to remove specific text+number values from a column. The values are 'US + a number between 1-10' so something like 'US 1.14' or 'US 1.25' etc. I've been able to remove values that just contain 'US' but not the ones with 'US [1-10]'. Does anyone know the SQL string to remove 'text + numbers' from a column?



the column is called Geo_Targeting and I've used the following code variations:



and GC.Geo_Targeting != '%US [0-9]%'

and GC.Geo_Targeting != 'US %[0-9]%'


Ideally, the code would remove any values that contain 'US' or 'US + number' only.






mysql sql







share|improve this question

























  • Are you trying to use REGEXP? At least would expect a LIKE. Not a equals/not equals.

    – ficuscr
    Jan 3 at 18:21











  • Possible dupe of: stackoverflow.com/questions/18780194/…

    – ficuscr
    Jan 3 at 18:23
















0















IN MySQL, I'm trying to remove specific text+number values from a column. The values are 'US + a number between 1-10' so something like 'US 1.14' or 'US 1.25' etc. I've been able to remove values that just contain 'US' but not the ones with 'US [1-10]'. Does anyone know the SQL string to remove 'text + numbers' from a column?



the column is called Geo_Targeting and I've used the following code variations:



and GC.Geo_Targeting != '%US [0-9]%'

and GC.Geo_Targeting != 'US %[0-9]%'


Ideally, the code would remove any values that contain 'US' or 'US + number' only.






mysql sql







share|improve this question

























  • Are you trying to use REGEXP? At least would expect a LIKE. Not a equals/not equals.

    – ficuscr
    Jan 3 at 18:21











  • Possible dupe of: stackoverflow.com/questions/18780194/…

    – ficuscr
    Jan 3 at 18:23














0












0








0








IN MySQL, I'm trying to remove specific text+number values from a column. The values are 'US + a number between 1-10' so something like 'US 1.14' or 'US 1.25' etc. I've been able to remove values that just contain 'US' but not the ones with 'US [1-10]'. Does anyone know the SQL string to remove 'text + numbers' from a column?



the column is called Geo_Targeting and I've used the following code variations:



and GC.Geo_Targeting != '%US [0-9]%'

and GC.Geo_Targeting != 'US %[0-9]%'


Ideally, the code would remove any values that contain 'US' or 'US + number' only.






mysql sql







share|improve this question
















IN MySQL, I'm trying to remove specific text+number values from a column. The values are 'US + a number between 1-10' so something like 'US 1.14' or 'US 1.25' etc. I've been able to remove values that just contain 'US' but not the ones with 'US [1-10]'. Does anyone know the SQL string to remove 'text + numbers' from a column?



the column is called Geo_Targeting and I've used the following code variations:



and GC.Geo_Targeting != '%US [0-9]%'

and GC.Geo_Targeting != 'US %[0-9]%'


Ideally, the code would remove any values that contain 'US' or 'US + number' only.






mysql sql




mysql sql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 3 at 18:17









Derviş Kayımbaşıoğlu

15.7k22042




15.7k22042










asked Jan 3 at 18:15









mpstringmpstring

208




208













  • Are you trying to use REGEXP? At least would expect a LIKE. Not a equals/not equals.

    – ficuscr
    Jan 3 at 18:21











  • Possible dupe of: stackoverflow.com/questions/18780194/…

    – ficuscr
    Jan 3 at 18:23



















  • Are you trying to use REGEXP? At least would expect a LIKE. Not a equals/not equals.

    – ficuscr
    Jan 3 at 18:21











  • Possible dupe of: stackoverflow.com/questions/18780194/…

    – ficuscr
    Jan 3 at 18:23

















Are you trying to use REGEXP? At least would expect a LIKE. Not a equals/not equals.

– ficuscr
Jan 3 at 18:21





Are you trying to use REGEXP? At least would expect a LIKE. Not a equals/not equals.

– ficuscr
Jan 3 at 18:21













Possible dupe of: stackoverflow.com/questions/18780194/…

– ficuscr
Jan 3 at 18:23





Possible dupe of: stackoverflow.com/questions/18780194/…

– ficuscr
Jan 3 at 18:23












1 Answer
1






active

oldest

votes


















1














This regex pattern will match the values you're looking for:



US( d+.?d*)?


It matches the characters 'US', followed by a space, followed by one or more digits, followed by zero or one '.' characters, followed by zero or more digits. The last ? means that it'll match zero or one of the entire digit group.



You can use it with the NOT_REGEXP function so that you only get values that don't match this pattern (note that '' characters need to be doubled in mySQL)



and GC.Geo_Targeting NOT_REGEXP 'US( \d+.?\d*)?'


If you need to match values that have this pattern only, you can use the '^' character to match the beginning of a line, and the '$' character to match the end.



and GC.Geo_Targeting NOT_REGEXP '^US( \d+.?\d*)?$'





share|improve this answer


























  • I gave this a shot and it's saying I have a syntax error. It says NOT_REGEXP isn't valid at its position

    – mpstring
    Jan 3 at 18:30











  • I don't have MySQL on my computer, so you'll need to do a little troubleshooting yourself. If you google how to use regex in MySQL you'll find plenty of results.

    – Josh Eller
    Jan 3 at 18:35











  • try withot underscore not regexp

    – Derviş Kayımbaşıoğlu
    Jan 3 at 18:39











  • Nice! That's gotten it a little closer but I have some values that say 'US, Canada' or US 1.3, France, Germany 2" etc and those are being removed as well. Is there a way to keep those and only remove values that have 'US' or 'US + number' only?

    – mpstring
    Jan 3 at 18:46













  • You can use ^ and $ to match the beginning and end of a line respectively. I edited my answer to reflect that.

    – Josh Eller
    Jan 3 at 19:02












Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54027666%2fhow-to-remove-values-that-contain-text-numbers-from-column-in-sql%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














This regex pattern will match the values you're looking for:



US( d+.?d*)?


It matches the characters 'US', followed by a space, followed by one or more digits, followed by zero or one '.' characters, followed by zero or more digits. The last ? means that it'll match zero or one of the entire digit group.



You can use it with the NOT_REGEXP function so that you only get values that don't match this pattern (note that '' characters need to be doubled in mySQL)



and GC.Geo_Targeting NOT_REGEXP 'US( \d+.?\d*)?'


If you need to match values that have this pattern only, you can use the '^' character to match the beginning of a line, and the '$' character to match the end.



and GC.Geo_Targeting NOT_REGEXP '^US( \d+.?\d*)?$'





share|improve this answer


























  • I gave this a shot and it's saying I have a syntax error. It says NOT_REGEXP isn't valid at its position

    – mpstring
    Jan 3 at 18:30











  • I don't have MySQL on my computer, so you'll need to do a little troubleshooting yourself. If you google how to use regex in MySQL you'll find plenty of results.

    – Josh Eller
    Jan 3 at 18:35











  • try withot underscore not regexp

    – Derviş Kayımbaşıoğlu
    Jan 3 at 18:39











  • Nice! That's gotten it a little closer but I have some values that say 'US, Canada' or US 1.3, France, Germany 2" etc and those are being removed as well. Is there a way to keep those and only remove values that have 'US' or 'US + number' only?

    – mpstring
    Jan 3 at 18:46













  • You can use ^ and $ to match the beginning and end of a line respectively. I edited my answer to reflect that.

    – Josh Eller
    Jan 3 at 19:02
















1














This regex pattern will match the values you're looking for:



US( d+.?d*)?


It matches the characters 'US', followed by a space, followed by one or more digits, followed by zero or one '.' characters, followed by zero or more digits. The last ? means that it'll match zero or one of the entire digit group.



You can use it with the NOT_REGEXP function so that you only get values that don't match this pattern (note that '' characters need to be doubled in mySQL)



and GC.Geo_Targeting NOT_REGEXP 'US( \d+.?\d*)?'


If you need to match values that have this pattern only, you can use the '^' character to match the beginning of a line, and the '$' character to match the end.



and GC.Geo_Targeting NOT_REGEXP '^US( \d+.?\d*)?$'





share|improve this answer


























  • I gave this a shot and it's saying I have a syntax error. It says NOT_REGEXP isn't valid at its position

    – mpstring
    Jan 3 at 18:30











  • I don't have MySQL on my computer, so you'll need to do a little troubleshooting yourself. If you google how to use regex in MySQL you'll find plenty of results.

    – Josh Eller
    Jan 3 at 18:35











  • try withot underscore not regexp

    – Derviş Kayımbaşıoğlu
    Jan 3 at 18:39











  • Nice! That's gotten it a little closer but I have some values that say 'US, Canada' or US 1.3, France, Germany 2" etc and those are being removed as well. Is there a way to keep those and only remove values that have 'US' or 'US + number' only?

    – mpstring
    Jan 3 at 18:46













  • You can use ^ and $ to match the beginning and end of a line respectively. I edited my answer to reflect that.

    – Josh Eller
    Jan 3 at 19:02














1












1








1







This regex pattern will match the values you're looking for:



US( d+.?d*)?


It matches the characters 'US', followed by a space, followed by one or more digits, followed by zero or one '.' characters, followed by zero or more digits. The last ? means that it'll match zero or one of the entire digit group.



You can use it with the NOT_REGEXP function so that you only get values that don't match this pattern (note that '' characters need to be doubled in mySQL)



and GC.Geo_Targeting NOT_REGEXP 'US( \d+.?\d*)?'


If you need to match values that have this pattern only, you can use the '^' character to match the beginning of a line, and the '$' character to match the end.



and GC.Geo_Targeting NOT_REGEXP '^US( \d+.?\d*)?$'





share|improve this answer















This regex pattern will match the values you're looking for:



US( d+.?d*)?


It matches the characters 'US', followed by a space, followed by one or more digits, followed by zero or one '.' characters, followed by zero or more digits. The last ? means that it'll match zero or one of the entire digit group.



You can use it with the NOT_REGEXP function so that you only get values that don't match this pattern (note that '' characters need to be doubled in mySQL)



and GC.Geo_Targeting NOT_REGEXP 'US( \d+.?\d*)?'


If you need to match values that have this pattern only, you can use the '^' character to match the beginning of a line, and the '$' character to match the end.



and GC.Geo_Targeting NOT_REGEXP '^US( \d+.?\d*)?$'






share|improve this answer














share|improve this answer



share|improve this answer








edited Jan 3 at 19:00

























answered Jan 3 at 18:25









Josh EllerJosh Eller

94027




94027













  • I gave this a shot and it's saying I have a syntax error. It says NOT_REGEXP isn't valid at its position

    – mpstring
    Jan 3 at 18:30











  • I don't have MySQL on my computer, so you'll need to do a little troubleshooting yourself. If you google how to use regex in MySQL you'll find plenty of results.

    – Josh Eller
    Jan 3 at 18:35











  • try withot underscore not regexp

    – Derviş Kayımbaşıoğlu
    Jan 3 at 18:39











  • Nice! That's gotten it a little closer but I have some values that say 'US, Canada' or US 1.3, France, Germany 2" etc and those are being removed as well. Is there a way to keep those and only remove values that have 'US' or 'US + number' only?

    – mpstring
    Jan 3 at 18:46













  • You can use ^ and $ to match the beginning and end of a line respectively. I edited my answer to reflect that.

    – Josh Eller
    Jan 3 at 19:02



















  • I gave this a shot and it's saying I have a syntax error. It says NOT_REGEXP isn't valid at its position

    – mpstring
    Jan 3 at 18:30











  • I don't have MySQL on my computer, so you'll need to do a little troubleshooting yourself. If you google how to use regex in MySQL you'll find plenty of results.

    – Josh Eller
    Jan 3 at 18:35











  • try withot underscore not regexp

    – Derviş Kayımbaşıoğlu
    Jan 3 at 18:39











  • Nice! That's gotten it a little closer but I have some values that say 'US, Canada' or US 1.3, France, Germany 2" etc and those are being removed as well. Is there a way to keep those and only remove values that have 'US' or 'US + number' only?

    – mpstring
    Jan 3 at 18:46













  • You can use ^ and $ to match the beginning and end of a line respectively. I edited my answer to reflect that.

    – Josh Eller
    Jan 3 at 19:02

















I gave this a shot and it's saying I have a syntax error. It says NOT_REGEXP isn't valid at its position

– mpstring
Jan 3 at 18:30





I gave this a shot and it's saying I have a syntax error. It says NOT_REGEXP isn't valid at its position

– mpstring
Jan 3 at 18:30













I don't have MySQL on my computer, so you'll need to do a little troubleshooting yourself. If you google how to use regex in MySQL you'll find plenty of results.

– Josh Eller
Jan 3 at 18:35





I don't have MySQL on my computer, so you'll need to do a little troubleshooting yourself. If you google how to use regex in MySQL you'll find plenty of results.

– Josh Eller
Jan 3 at 18:35













try withot underscore not regexp

– Derviş Kayımbaşıoğlu
Jan 3 at 18:39





try withot underscore not regexp

– Derviş Kayımbaşıoğlu
Jan 3 at 18:39













Nice! That's gotten it a little closer but I have some values that say 'US, Canada' or US 1.3, France, Germany 2" etc and those are being removed as well. Is there a way to keep those and only remove values that have 'US' or 'US + number' only?

– mpstring
Jan 3 at 18:46







Nice! That's gotten it a little closer but I have some values that say 'US, Canada' or US 1.3, France, Germany 2" etc and those are being removed as well. Is there a way to keep those and only remove values that have 'US' or 'US + number' only?

– mpstring
Jan 3 at 18:46















You can use ^ and $ to match the beginning and end of a line respectively. I edited my answer to reflect that.

– Josh Eller
Jan 3 at 19:02





You can use ^ and $ to match the beginning and end of a line respectively. I edited my answer to reflect that.

– Josh Eller
Jan 3 at 19:02




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54027666%2fhow-to-remove-values-that-contain-text-numbers-from-column-in-sql%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

Mossoró

Image
  Nota: Para outros significados, veja Mossoró (desambiguação). Município de Mossoró "Capital do Oeste Potiguar" [ 1 ] "Capital nacional do Semiárido" [ 2 ] [ 3 ] "Capital Cultural do Rio Grande do Norte" [ 1 ] "Terra de Santa Luzia" [ 4 ] "Terra do Sol, do Sal e do Petróleo" [ 5 ] "Terra da Liberdade [ 6 ] " Centro de Mossoró, com a Praça Vigário Antônio Joaquim e, ao fundo, a câmara municipal de vereadores (esquerda) e Catedral de Santa Luzia (direita) Bandeira Brasão Hino Fundação 15 de março de 1852 (166 anos) Gentílico mossoroense Padroeiro(a) Santa Luzia Prefeito(a) Rosalba Ciarlini Rosado (PP) (2017 – 2020 ) Localização Localização de Mossoró no Rio Grande do Norte Mossoró Localização de Mossoró no Brasil 05° 11' 16" S 37° 20' 38" O 05° 11' 16" S 37° 20' 38" O Unidade federativa Rio Grande do No...
Read more

Error while reading .h5 file using the rhdf5 package in R

Image
0 I'm importing a .h5 file into R by using the rhdf5 package. But a warning message showed and I found the DATE_TIME column of my data is NA when I view the imported file. Warning message: In H5Dread(h5dataset = h5dataset, h5spaceFile = h5spaceFile, h5spaceMem = h5spaceMem, : h5read for type 'TIME' not yet implemented. Values replaced by NA's. It's all normal when I use Vitables (Python) to open the .h5 file. But the DATE_TIME column is NA with HDFViewer open it. My code is: library(rhdf5) test <- h5read("mytestdata.h5", "/results") View(mydatatest) I want to use R to open .h5 file. Please give me some help! Thanks in advance! r hdf5 rhdf5 ...
Read more

Pushsharp Apns notification error: 'InvalidToken'

Image
1 1 I am currently working on automatic updates for Passbook (wallet) tickets and am experiencing some trouble using the Pushsharp library by Redth. I am using a Push notification certificate from the apple developer portal. I have tried to export my certificate as .p12, .pem and tried to use only the private key as .12 or .pem but nothing works. This is my full certificate (information is blanked out for security reasons): https://cdn.pbrd.co/images/HUJtb7b.png I dont have enough reputation to post images so a link is all i can provide. var succeeded = 0; var failed = 0; var attempted = 0; var config = new ApnsConfiguration(ApnsConfiguration.ApnsServerEnvironment.Sandbox, ConfigManager.CertPath + "PushCertificateV2.p12", ConfigManager.lvppass, fa...
Read more