Member Avatar for sivak

i have one employee tabel with 4 coloums
emp name emp id employee salary tax
john 001 30k
smith 112 15k
joe 113 45k
i have to fill tax coloum =25% when salary >=30k otherwise just --
can any one tell me the query ?

  • 2 Contributors
  • 2 Replies
  • 95 Views
  • 18 Hours Discussion Span
  • Latest Post Latest Post by JerryShaw

Dani AI

Generated

@svivak posted a salary column that looks like text with a "k" suffix, which prevents simple numeric comparisons. Best practice is to store salary as a numeric column (e.g., INT cents or DECIMAL), but a safe fix on existing data is: (1) parse the text to a numeric amount, (2) test the parsed values with SELECT, and (3) then UPDATE the tax field using a CASE expression.

A compact MySQL 8.0+ UPDATE that parses common formats, treats a trailing "k/K" as thousands, and sets tax to the literal string 25% when computed salary >= 30000 (otherwise --):

UPDATE Employee
SET tax = CASE
  WHEN (CAST(REGEXP_REPLACE(TRIM(LOWER(salary)), '[^0-9.]', '') AS DECIMAL(12,2))
        * (CASE WHEN RIGHT(TRIM(LOWER(salary)),1) = 'k' THEN 1000 ELSE 1 END)
       ) >= 30000
  THEN '25%' ELSE '--' END;

Notes and troubleshooting: run a SELECT that computes the parsed numeric value first to confirm formats and edge cases (NULLs, commas, "1.5k", trailing spaces). REGEXP_REPLACE requires MySQL 8+. For older MySQL, use careful combinations of REPLACE, TRIM, LOWER or replace 'k' with '000' only when safe. For accuracy, add a new numeric column, populate it from the cleaned values, and compute tax numerically (salary_numeric * 0.25) rather than storing percentages as text. correctly flagged the "K" suffix, but his posted UPDATE used a 1.25 multiplier (that would inflate values); a 25% tax means multiplying salary by 0.25 or storing the string 25%. Always test on a copy and wrap changes in a transaction or backup first.

MySQL function references: string functions · CAST/CONVERT documentation

Recommended Answers

All 2 Replies

Member Avatar for JerryShaw

Do you mean that your data actually contains the charcter 'K' in the salary value ?
(that would be ugly)
Assuming that it is true:

UPDATE dbo.Employee
SET TAX = ( 1.25 * Convert(int,substring(Salary,1,len(Salary)-1) )
WHERE Salary > '30K'

**free handed, so perform some tests on a temp table before using it on a real system.

// Jerry

Member Avatar for JerryShaw

UPDATE dbo.Employee
SET TAX = ( 1.25 * (Convert(int,substring(Salary,1,len(Salary)-1) * 1000 ))
WHERE Salary > '30K' OR len(Salary > 3)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.