时间戳

首页 > 解决方案 > SQL麻烦！需要 IF/CASE 帮助

sql - SQL麻烦！需要 IF/CASE 帮助

问题描述

所以。重新排列表格中的列格式以导入某些软件。以前的 DB 中有一个性别列，其中包含 M、F 或 U。新软件不会将 U 作为有效值，因此我需要一种方法来空白，同时保持 M 和 F 值相同。

SELECT Id AS Customerrefid,
       SUBSTRING(Name, CHARINDEX(' ', "Name") + 1, LEN(Name) - (CHARINDEX(' ', "Name") - 1)) AS Surname,
       LEFT("Name", CHARINDEX(' ', "Name")) AS Forename,
       '0' AS Barcode,
       "Address" AS "Address",
       Postcode AS Postcode,
       'UK' AS Countrycode,
       Dob AS Dateofbirth,
       Gender AS Gender,
       ' ' AS "Password",
       Comment AS Notes,
       ' ' AS "Skintype",
       Homephone AS Hometel,
       ' ' AS "Worktel",
       Mobilephone AS Mobiletel,
       Email AS Emailaddress,
       'FALSE' AS Allowemail,
       'FALSE' AS Allowsms,
       'FALSE' AS Agreementsigned,
       ' ' AS Pin
FROM Customer;

select * from customer

标签： sqlsql-serverselect

解决方案

You could use a case expression to turn Us in to nulls:

CASE gender WHEN 'U' THEN NULL ELSE gender END AS gender

推荐阅读