Database

Trustapple wrote:

It still is not working

select customername,
case 
   when CHARINDEX(',', customername)>0 then
      substring(customername,1,CHARINDEX(',', customername) -1)
   else 
      substring(customername,1,CHARINDEX(' ', customername) -1) 
end as [firstname ],
case 
   when CHARINDEX(',', customername)>0 then
      SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername)) 
   else 
      SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername)) 
end AS [LastName]
from TableName

Trustapple wrote:

Thanks for you trying to help

Trustapple wrote:

I will be able to access it only after 5hours.As soon as i try thi s query i will post a reply and let you know

Trustapple wrote:

Merry Christmas

select customername,
-- get the last name
case
    when CHARINDEX(',', customername) > 0 then
      SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
    when CHARINDEX(' ', customername) > 0 then
      SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
    else
      '' -- neither comma nor space found
end as [lastname],
-- get the first name
case
    when CHARINDEX(',', customername) > 0 then
      SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
    when CHARINDEX(' ', customername) > 0 then
      SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
    else
      '' -- neither comma nor space found
end AS [firstname]
from TableName

Ben Fair wrote:

SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter

Trustapple wrote:

Thanks

Trustapple wrote:

along with our query i want it to also do this that is if the customername is Mika (ie the name is only one word and it doesnot contain any ',' or ' ')Then it should update to FirstName.....

update TableName
set lastname 
    = case
         when CHARINDEX(',', customername) > 0 then
            SUBSTRING(customername, 1, CHARINDEX(',', customername) - 1) -- comma is delimiter
         when CHARINDEX(' ', customername) > 0 then
            SUBSTRING(customername, CHARINDEX(' ', customername) + 1, LEN(customername) - CHARINDEX(' ', customername) + 1) -- space is delimiter
         else
            TableName.LastName --<code> no delimitter so don't change lastname in the table</code>
      end,
   firstname 
   = case
        when CHARINDEX(',', customername) > 0 then
           SUBSTRING(customername, CHARINDEX(',', customername) + 1, LEN(customername) - CHARINDEX(',', customername) + 1) -- comma is delimiter
        when CHARINDEX(' ', customername) > 0 then
           SUBSTRING(customername, 1, CHARINDEX(' ', customername) - 1) -- space is delimiter
        else
           customername -- <code>no delimiter so use the full data to firstname</code>
        end ...

Trustapple wrote:

Thanks for your help

dl4gbe wrote:

Without the order by clause the query is working.

dl4gbe wrote:

I can't change system tables space settings

dl4gbe wrote:

I can't look at the execution plan. The query tool we are using does not allow that

SELECT alias1.Result
FROM (SELECT (ColA - ColB) AS Result 
      FROM    Table) alias1
WHERE alias1.result > 0

toticow wrote:

I was just kind of hoping there was a way to store the value in a temp variable on a row by row basis

toticow wrote:

so thanks

WITH Results AS
(
    SELECT ColA - ColB [Result]
      FROM TABLE
)
SELECT *
  FROM Results 
 WHERE Result > 0

SELECT ColA - ColB [Result]
  INTO #temp
  FROM TABLE

SELECT *
  FROM #temp
 WHERE Result > 0

DROP TABLE #temp

DECLARE @temp TABLE (Result int)

INSERT @temp
SELECT ColA - ColB [Result]
  FROM TABLE

SELECT *
  FROM @temp
 WHERE Result > 0