locked
store numbers with variable RRS feed

  • Question

  • User2146470223 posted

    Hi,

    Is there a way to store a number within a string so that with the next query it would be easier to see that this part can be easily replaced by number?

    very easy example:

    36 °C -> x °C

    or even with regex: [0-9] °C

    In usage the string can be much more complex or the number nested way further in the middle of a complex string. It would be just to be able to call a string with a certain 'like' structre which could contain any possible number.

    taking the above example: find that stored string even if I look for "9 °C" or would it be the best solution to only look for regex number "[0-9] °C"?

    Though it would be easier for me if it could be stored as such a recognizable variable to the system.

    I hope you get the point.

    Thanks in advance,

    Pascal

    Monday, April 6, 2015 7:19 PM

Answers

  • User1711366110 posted


    36 °C -> x °C
    or even with regex: [0-9] °C

      As per your case, I suggest you to create RULE with regex & have to bind in particular column in your table like below example :

    --------Table----------
    create table weather(
    location varchar(50),
    degree varchar(50))
    
    ------Create Rule -----------------
    CREATE RULE [dbo].[Temperature] 
    AS
    @column like '[0-9][0-9] °C';
    
    -----Bind Rule for particular table column--------------
    EXEC sp_bindrule  '[dbo].[Temperature]','weather.degree'
    
    insert into weather(location,degree) values('Texas','36 °C');
    insert into weather(location,degree) values('Los angeles','06 °C');
    
    select * from weather where degree like '[0-9][0-9] °C';
    
    -----UnBind Rule for particular table column------------
    EXEC sp_unbindrule  'weather.degree'
    
    -----Drop Rule------------
    drop rule [dbo].[Temperature] 
    
    

    Click here to know more about RULE in sqlserver

    --
    with regards,
    Edwin

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 6, 2015 10:01 PM
  • User2146470223 posted

    Hi Edwin,

    Sorry for the late reply but had quite some stress at work before I could go on with my little programming project.

    In fact your idea can still be used, even if I don't know where the numbers will be in the string. As soon as numbers are used in a string I will simply replace them with regex to query for the string.

    I have 2 cars.                   ->  … where column = 'I have I have \d*.?\d*|\d*,?\d* cars\.'

    We had to pay 2,51 EUR.  ->  … where column = 'We had to pay \d*.?\d*|\d*,?\d* EUR\.'

    They paid 1.10 USD.        ->  … where column = 'They paid \d*.?\d*|\d*,?\d* USD\.'

    So numbers will automatically be replaced in the query by \d*.?\d*|\d*,?\d* to get all possible numbers or decimals, no matter which locale settings were used when writing the string, in one query.

    Thanks,

    Pascal

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 20, 2016 10:22 PM

All replies

  • User1711366110 posted


    36 °C -> x °C
    or even with regex: [0-9] °C

      As per your case, I suggest you to create RULE with regex & have to bind in particular column in your table like below example :

    --------Table----------
    create table weather(
    location varchar(50),
    degree varchar(50))
    
    ------Create Rule -----------------
    CREATE RULE [dbo].[Temperature] 
    AS
    @column like '[0-9][0-9] °C';
    
    -----Bind Rule for particular table column--------------
    EXEC sp_bindrule  '[dbo].[Temperature]','weather.degree'
    
    insert into weather(location,degree) values('Texas','36 °C');
    insert into weather(location,degree) values('Los angeles','06 °C');
    
    select * from weather where degree like '[0-9][0-9] °C';
    
    -----UnBind Rule for particular table column------------
    EXEC sp_unbindrule  'weather.degree'
    
    -----Drop Rule------------
    drop rule [dbo].[Temperature] 
    
    

    Click here to know more about RULE in sqlserver

    --
    with regards,
    Edwin

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 6, 2015 10:01 PM
  • User2146470223 posted

    Hi Edwin,

    Well it's not about a table for temperatures, as I said this was only a very simple example. It can also be even much more complex.

    e.g.: In the year 2000 5 million people had to leave their homes within 2 days as a boming of their city.

    So all the figures should be replaced. It could be a phrase from every possible field like engineering, sciences, maths, economy aso. ;)

    But if I get you right, storing is not possible but only using regex to query the result. The problem is that I never know in advance where the figures will be in a string, so I guess every time I concatenate the query from an existing string I will have to replace figures with regex when they come up.

    regards,

    Pascal

    Tuesday, April 7, 2015 4:21 AM
  • User2146470223 posted

    Hi Edwin,

    Sorry for the late reply but had quite some stress at work before I could go on with my little programming project.

    In fact your idea can still be used, even if I don't know where the numbers will be in the string. As soon as numbers are used in a string I will simply replace them with regex to query for the string.

    I have 2 cars.                   ->  … where column = 'I have I have \d*.?\d*|\d*,?\d* cars\.'

    We had to pay 2,51 EUR.  ->  … where column = 'We had to pay \d*.?\d*|\d*,?\d* EUR\.'

    They paid 1.10 USD.        ->  … where column = 'They paid \d*.?\d*|\d*,?\d* USD\.'

    So numbers will automatically be replaced in the query by \d*.?\d*|\d*,?\d* to get all possible numbers or decimals, no matter which locale settings were used when writing the string, in one query.

    Thanks,

    Pascal

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 20, 2016 10:22 PM