2

I am in need of some assistance/advice please.

I have a query in MSSQL as an openquery to a Postgres Linked Server; a CASE statement actually, in a query that allowed me to check to see if a value in a column could be cast as 'FLOAT' and if not, return a '1'.

I need to be able to use something like this as this column ('OLD_LABEL') contains user entered data that isn't standardized (values range from 'user' to 'North' to NULL to 12345 to 'SET1024'). So to pull back records that have a specific value (to not break automation, I need REAL numbers ranging from '0' to '9999.995') I used the below:

CASE 
WHEN OLD_LABEL IS NULL THEN ''0''
WHEN try_cast(OLD_LABEL AS FLOAT) IS NULL THEN ''1''
ELSE OLD_LABEL END AS NEW_LABEL

However, it was moved to a Postgres db and now my case expression and subsequent automation won't work (since try_cast is a built in function to MSSQL). What I want to do is pretty simple in theory - however I am not familiar with Postgres enough to come up with a solution on my own

How do I say this as when value isn't a recognizable/real number in the column 'OLD_LABEL' then set/replace value to '1'

The caveat is that when the existing value in 'OLD_LABEL' is not a 0 or NULL, and is an actual number then I need to keep/use that number in the 'NEW_LABEL' column - otherwise replace that value with a '1'

Is there a way to do this? Additionally - I only have 'READ' access to the Postgres DB, so anything that would require table creation, functional changes to the DB, etc are out of my reach (and no way to get the permissions to do so as I don't 'own' the DB I am querying) - I do have admin access to the MSSQL DB I am running the openquery from however.

Improve this question
1
  • Without creating a function that mimics the try_cast feature there is no easy way. See here or here
    – a_horse_with_no_name
    Dec 17, 2021 at 6:29

1 Answer 1

Reset to default
0

If you are familiar with regular expressions, then it is fairly straightforward to check your input for validity and then apply your classification rules.

db fiddle

SELECT old_label,
       CASE WHEN old_label IS NULL THEN '0'
            ELSE COALESCE(
                   substring(old_label from '^ *([0-9]*\.?[0-9]*) *$'),
                   '1'
                 )
       END AS new_label
  FROM dodgy;

This particular pattern match allows for leading and trailing space characters and requires an integer or floating point number without resorting to scientific notation (since you specified a range of numbers that didn't require that complication.) substring() returns NULL if there is no match; COALESCE returns the first non-NULL if any.

If you wish to adjust the pattern definition regexp documentation may help.

Improve this answer
4
  • Appreciate the assist - want to make sure I understand the supplied answer - Because we are using COALESCE w/ the regexp_match, what is happening is that if nothing matches the string value (so has to match on a REAL number) COALESCE will return 'NULL' which will result in substituting a '1' in place of the value that isn't a REAL number - if a string match is found (REAL number)- COALESCE will return that value instead - do I have that logic correct?
    – ripvw32
    Dec 17, 2021 at 3:34
  • Right idea, but the functions are nested the other way around: regexp_match() returns an empty list if there are no matches, and selecting element [1] of an empty list is NULL. COALESCE returns the first non-NULL value it sees if any.
    – gwaigh
    Dec 17, 2021 at 5:17
  • 1
    If you want to extract a string based on a regex, substring(... from ..) is typically easier to work with.
    – a_horse_with_no_name
    Dec 17, 2021 at 10:06
  • @gwaigh Thank you very much for the explanation and answer! Truly appreciate it!!
    – ripvw32
    Dec 17, 2021 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.