For example,
select fieldA form tableA where fieldA = 'aaa'
I got following output
fieldA
---------
aaa
aAa
AAA
AAa
...
if I want select only the lower case 'aaa', how can I do that?
Currently i have 2 type of data
A and a
But when i try to:
select * from tableA where col = 'a'
then all record with A and a will be selected, any way to avoid it and select only record with col ='a'?
I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies View RelatedI need to split a string in two if there are lowercase characters atthe end of it.For example:'AAPLpr' becomes 'AAPL' and 'pr''Ta' becomes 'T' and 'a''MSFT' becomes 'MSFT' and '''TAPA' becomes 'TAPA' and ''I am using SQL 2000. I read about "collate Latin1_General_CS_AS" butnot sure if I can use that outside of a select statement.Thank you in advance for any help.
View 2 Replies View Relatedhi i want to select * from table1 where name =petter?now if there is many type of petter in table linke PETTER , Petter And petter which record will come in display?if i want all this three (PETTER,Petter,petter) will come in display which command is for this ??? regard
View 4 Replies View RelatedHi,
My database is case insensitive. However, the application is case sensitive for the data in the table.
I need to find out the data from the table where the data is stored in lower case.
Here is the sample data:
Table: inv_manager
column: sku varchar(5)
Data: 1134X
1135x
1123a
b145Y
I just need query to return row 2, 3, 4.
Here is my query:
select sku from inv_manager where sku like lower('%[a-z]%')
The problem is it returns all the rows from the table including first one.
How can I return just the data with lower case ?
Thanks
Hi
I can't create unique index like that:
1 - create table abc ( colZ varchar(10) )
2 - insert into abc values ( "test")
3 - insert into abc values ("Test")
4 - create unique index idx_abc on abc ( colZ ) -- This doesnt work
.... duplicate key was found for object name abc......
Is not there difference between "Test" and "test"?
Can I work around this?
cheers,
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
Hi there ;
I've a photo viewer which shows the image based on a datetime QueryString. I've a couple of buttons like next, prev, last, .... .
in the "prev" for instance, i want to select the image with a datetime that is lower than the current image datetime, and is the biggest one of course.but i'm stuck in it!
By the way i use .net 2.0 & Sql 05 Express
thanks in advance
What I'm trying to select is the closest value from a list given by a parameter or select the matched value.
declare @compare as int
set @compare = 8
declare @table table
(
Number int
)
insert into @table
values(1),
(2),
(3),
(4),
(5),
(10)
If the parameter value match one of the values from the table list, select that matched one.If the value does not exist in the table list, select the closest lower value from the table list, in this case, it would be value 5.
I need to write a select statement that take the upper table and select the lower table.
View 3 Replies View RelatedI am using a select statement likeSELECT ENAME FROM EMPTABLE WHERE ENAME ="vishal"I am getting the result as 1 row affected.Ename----------VishalThe Problem is the query I have passed is "vishal"(lower case) andgetting the output as "Vishal"(V is Upper Case)How do I solve this problem if I want to go with case sensitiveretrival of data.I have gone thru some string functions available but could not findone.Any help would be appreciated..regardsvishal.
View 3 Replies View RelatedI am attempting to create a view to bring together multiple joined tables based ona one to many relationship
Eg Table1 contains code, address
Table2 contains code, financial details
table3 contains code, financial history
This view is then going to be used to update a table with only one record for each 'code'. i.e. NewTable = code, add1, add2, city, prov, postal, financialvalue, history value1, history value2
My current stumbling block is:
One of the fields in table1 is a free format address field (address).
eg. could be (street addres, city prov, postal)
or
could be (street address 1, address2, address 3, city prov, postal)
I want to be able to assign individual components of the address to corresponding fields
if # of commas = 2 then
address1 = substring(address,1, position of first comma)
cityprov = substring(address,possition of first comma, position of second comman)
postal = substring(address rest of field)
I have a UDF which returns the number of commas but I cannot figure out how to use either a nested case statement to parse the string...
ANy ideas on how best to accompish this?
( this table is needed for some leacy software which can only handle one record with all infor....
greg
Ho can I convert first letters of a string to Upper Case (i.e. UNITED KINGDOM - Untited Kingdom). I have country names table which has all entries in uper case. This makes a select box very larg and unproportional.
Thanks in advance for the help.
Php95saj
Dear all,
I have building an authitication mecanism through a database standard login table as :
UserName varchar
PassWord varchar
UserId Identity
From my client application I need to validate the username and the password.
For that I have a store procedure with the following statement :
SELECT @loginID = Id from [NomosConfig].[dbo].Users
WHERE [UserName]=@UserName
AND [PassWord] = @pw
From that point this works well except that for the passowrd, whre of course there will be no difference between:
PassWor = myPassw or MYPASSW
How can I check in password field in order that it is case sensitive....?
Later on, the password will be encrypted of course, will it behaves similar ?
thnaks for help
regards
serge
How would I convert an expression like on of these to all upper case first letters with remaining letters lower case? VB has a function for that but sql doesn't seem to. I thought about having a loop go through each character to check for spaces. I've written a couple of similar pieces of code in VB when a while ago, but is there a better way? Thanks :)
Just a couple of typical examples of how the data should appear ~
payment, credit card ==> Payment, Credit Card
butcher & singer ==> Butcher & Singer
Select * FROM TAB1 wher AI_TEXT_VALUE like '%PE%'
The unfortunate thing is i get entries like <Perennial allergic rhinitis>
I am looking specifically for the term PE ( All upper case ) But keep in mind, I would need to use the LIKE operator in my where clause.
( just like in the example above )
I have a scenario which i must check if the value of the inputted word is exact (case sensitive) in a string. And print the word if correct. using a user defined function.
Example:The quick brown fox jump over the lazy dog.
If the user enter "jump" it must print "jump".
If the user enter "Jump" it must print "jump" which is the correct word.
Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max
View 3 Replies View RelatedI am required to find or check that a specific word exists in string or not.
Suppose I have to find the word 'st' than I need the result true if and only if the following occurrences are there.
1. 'St is valid;' -> true
2. 'DOB is valid;ST is invalid;' -> true
3. 'DOB is valid; ST is invalid;' -> true
4. 'DOB is valid;invalid ST;' -> true
5. 'DOB is valid; invalid ST;' -> true
6. 'DOB is valid; invalid STate;' -> false
Means the exact ST should be search. It is not free text search.
T-SQL is needed to be used in select statement with case using PATINDEX, RegEx or any suitable t-sql command.
Hi, i have just written this:
select distinct department = case department
when 'ict' then 'Partnership Office'
when 'finance' then 'Partnership Office'
when 'Employee Development' then 'Partnership Office'
when 'Contract Compliance & MIS' then 'Partnership Office'
when 'Quality, Standards & Effective' then 'Partnership Office'
when 'Partnership administration Exe' then 'Partnership Office'
when 'Partnership Executive' then 'Partnership Office'
when 'Personnel' then 'Partnership Office'
when 'Operations' then 'Partnership Office'
when 'Quality Standards and Effectiv' then 'Partnership Office'
when 'Marketing and Communications' then 'Partnership Office'
when 'Marketing and Information' then 'Partnership Office'
else department end from personnel_records
is there some way to do this on one line, like
...
when in ('ict','finance','personnel', etc etc) then 'Partnership Office'
else
... ?
TIA
hi, this is my procedure.
CREATE procedure yard_retrieve
(
@yard varchar(50),@vessel varchar(50),@customer varchar(50)
)
AS
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@yard and category_code=(select category_code from categoryMaster where category_description='Yard') )
select 'found' as result
else
select 'not found' as reault
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@vessel and category_code=(select category_code from categoryMaster where category_description='vessel'))
select 'found' as reault1
else
select 'not found' as reault1
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@customer and category_code=(select category_code from categoryMaster where category_description='customer'))
select 'found' as reault2
else
select 'not found' as reault2
end
GO
if i run this procedure i need the result as following
result result1 result2
found notfound found
is this posible to do using select case ? please help me to get my need please
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
Hi All,
I have SQL query as:
SELECT CASE @sortcolumn
WHEN 'TYPE' THEN TYPE_TXT
WHEN 'DATE' THEN CHECKOUT_TS
WHEN 'ACCT' THEN ACCT_NBR
WHEN 'FIRST' THEN FIRST_NM
WHEN 'LAST' THEN LAST_NM
WHEN 'DAYS' THEN DATEDIFF(dd, CHECKOUT_TS, GETDATE())
ELSE DELIVER_ID END
FROM REQUEST
If @sortcolumn has 'DATE' or 'DAYS' it runs fine....any other value, I get an exception "Conversion failed when converting datetime from character string". All columns except CHECKOUT_TS are of type varchar.
Any help would be greatly appreciated.
Thanks,
Shashi
I need to do the following so that I can convert a Null value to 0 in my db... however I am getting errors "Incorrect Syntax near '='.
Facilities.PortfolioID AS PortfolioID =
CaseWhen Facilities.PortfolioID = Null then Facilities.PortfolioID = 0
End,
Hi, i've got this stored procedure:
@LidFederatieNummer varchar(20),
@ClubID int,
@LidClubNummer varchar(50),
@PersoonNaam varchar(100),
@ClubStamnummer varchar(10),
@ClubNaam varchar(50),
@SeizoenID int,
@ShowAllJN bit,
@LidFederatieJN int,
@CategorieID int
AS
BEGIN
IF @CategorieID IS NOT NULL BEGIN
DECLARE @LicentieVerplicht int
SET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
END
IF (@ShowAllJN = 0)
BEGIN
SET RowCount 100
END
CASE @LicentieVerplicht
WHEN '1' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 1
Order By Persoon
END
WHEN '2' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN)
ORDER BY Persoon
END
WHEN '3' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 0
Order By Persoon
END
According the value of my @LicentieVerplicht variable:
DECLARE @LicentieVerplicht intSET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
My where clausule should change... how can i achieve this cause my case is not working because it is not inside the select statement
Hi everyone,
I have the following problem withj this query:
SELECT DocNumber,
Title,FirstName,LastName,Paper,
ANNO,Location,Keywords,
URL= case WHEN URL IS NULL THEN
'|<a
href=''https://illiad.library.nova.edu/illiad/FNN/illiad.dll?ILLiadRedirect=OpenURL&genre=thesis&isbn='+CONVERT(varchar,DocNumber)+'&title='+CONVERT(varchar,Title)+'&rft.pub='+CONVERT(varchar,Program)+'&rft.edition='+
CONVERT(varchar,Paper) +'&date='+ CONVERT(varchar,ANNO)+
'&aulast=' + CONVERT(varchar,Lastname) + '&aufirst=' +
CONVERT(varchar,Firstname)+
'&url_ver=Z39.88-2004&rfr_id=info:sid/MARPS:Practicum''target=''blank'')>
Order through ILLiad</a>' ELSE URL END
FROM DocTable1 WHERE FREETEXT....
When URL is null, I don't get the string showing. It works properly only in the query analyzer.
Any ideas?
Christian
I'm using a 'searched case' however I don't care which method I use.
I'm open to using anything....
I just want to correct a problem in my pivot table where I need to create a ficticous eventdate for 'S' records, those that do not have an event date in the past 7 days.
-- eventdate is a datetime column
select User
,res =
case res --event result
when 'S' then '0' --S generic non-event
when 'SUC' then '1' --successfull event
end
,eventdate =
case eventdate
when enventdate < getdate()-8 then getdate()-8
end
from eventlog_1 --my view of events
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '<'.
TIA
JeffP...
I am doing a mail merge from data returned from a query. My problem is one of my fields is to long for the document. I want to be able to break up the line into 2 sections. I'm assuming I can do this within the case statement but am unsure how.
This is the code I have. I want the break to come before the "at".
case
when rb.building in (9420,420) then 'DEERFIELD SUMMER SCHOOL at BAKER WOODS #99'
when rb.building=9722 then 'JAMES SUMMER SCHOOL' 'at JOHN MARSHALL MS'
when rb.building=9723 then 'NORTHWEST SUMMER SCHOOL at CRISPUS ATTUCS MS'
when rb.building=9717 then 'BROAD SUMMER SCHOOL at SIDENER MS'
when rb.building=9715 then 'MANUAL SUMMER SCHOOL at MCFARLAND MS'
else upper(trim(rb.building_name)) end,
Hey guys, i'm trying to use a CASE within a select but i'm not sure how to get started.
Here is my select so far, i'm inserting into a temp table that is used within a loop to gather data from my trigger:
Code:
insert into @temp_par
select CONVERT(varchar(20),d.PAT_ID),
ep.EXT_PAT_ID,
d.DRUG_DESC_ID,
d.ALLERGY_CD,
CONVERT(varchar(20),d.PAR_DT,120),
ISNULL(d.PAR_TXT, 'NULL'),
ISNULL(REPLACE(m.DRUG_NM,'"','^"'),'NULL'),
ISNULL(m.DOSAGE_FORM_CD,'NULL'),
ISNULL(m.STRGTH,'NULL'),
ISNULL(m.STRGTH_UM,'NULL')
from deleted d --PATIENT_PRIOR_ADVERSE_REACTIONS
join EXTERNAL_PATIENT ep on ep.PAT_ID = d.PAT_ID
join MEDNAME m on m.DRUG_DESC_ID = d.DRUG_DESC_ID
I would like to use a CASE for the d.ALLERGY_CD column. The data in this column is usually a 1,2,3,4,5 but i'd like to convert those to their real data, for example:
Code:
1 = A
2 = B
3 = C
4 = D
5 = E
Any help?
Limitations: Needs to be single sql statement for exec, no udf/stored proc
Objective: select aggregate count from tblPerson grouped by s_fk, division,Desc, areaDesc with rollup for totals
if mail_goes_to=1, get the s_fk value via tblPerson/tblAffiliation/tblOrg/tblCity/tblCounty
if mail_goes_to=2, get the s_fk value via tblPerson/tblCity/tblCounty, skipping tblAffiliation/tblOrg
What's the best method? A case statement? Would a cross-join work? Thanks so much for any and all advice!
tblPerson: person_pk, affiliation_fk; home_city_fk, mail_goes_to
tblAffiliation: affiliation_pk, organization_fk
tblOrg: organization_pk, city_fk
tblCity: city_pk, county_fk
tblCounty: county_pk, s_fk
For resulting display only:
tblPersonArea: personArea_pk, area_fk, person_fk
tblArea: area_pk, areaDesc, division_fk
tblDivision: division_pk, divisionDesc
Hello !
For MS SQL 2000/2500
I am having a table ::
User
Ident int NOT NULL,
Numb int NOT NULL,
Info nvarchar(100) NOT NULL,
with this query ::
SELECT Numb, Info, Ident
FROM Users
WHERE (Numb IN (100,200, 300))
ORDER BY Ident
I get ::
Numb | Info | Ident
100 | dgwrg | 1
200 | dgeth yiuyi | 1
300 | Agt hgn ngtn | 1
100 | jttytujky | 2
200 | yjtut iuiu | 2
300 | dgd 2gggwekew | 2
what I need is something like::
SELECT
(CASE WHEN Users.Numb = 100 THEN Users.Info END) AS InfoA,
(CASE WHEN Users.Numb = 200 THEN Users.Info END) AS InfoB,
(CASE WHEN Users.Numb = 300 THEN Users.Info END) AS InfoC,
Ident
FROM Users
WHERE Ident = Ident (for the same Ident)
to get ::
InfoA | InfoB | InfoC | Ident
dgwrg | dgeth yiuyi | Agt hgn ngtn | 1
jttytujky | yjtut iuiu | dgd 2gggwekew | 2
Actually I am getting a NULL value everywhere
of course I cannot use MAX(CASE WHEN ... , or I am getting only a few rows and not all
thanks a ton for helping
OK so I want to use datepart(month,getdate() with a Case statement with this logic:
SELECT CASE
datepart(month,getdate()='8' then
SELECT *
FROM [Master]
WHERE [Month]='Jul'
END
OK what's wrong with the code?