March 22, 2013 at 3:31 am
Hi ,
I have a table.Table name of LG_XXX_. I have a "Definition_" string column in Table.Locate the first space character, this column space.The left side of the "name" as the right side of the "LastName" column.
For Example ;
Now position :
/*(Field Name)*/_____ Definition_
___________________ ==========
(Value)_____________ ERSOY AYDIN
Next position :
(Field Name1) ___ (Field Name2)
NAME___________ LASTNAME
=========_____ ========
ERSOY__________ AYDIN
Is there ready function for a column to break up into two ?
Best regards
March 22, 2013 at 4:31 am
if i have understood your question, you have a table with three columns one contains forename and surname combined as a fullname and then you have a name and lastname column you want to populate from the fullname column. This sql shoudl do it but assumes that the name is split from the first space found:
CREATE TABLE #LG_XX_
(
Definition_ VARCHAR(250)
,Name VARCHAR(100)
,LastName VARCHAR(100)
)
GO
INSERT INTO #LG_XX_ (Definition_) VALUES ('john Smith') ,('john Smith'), ('james Smith'), ('Jeramy')
GO
UPDATE #LG_XX_ SET
Name = CASE WHEN CHARINDEX(' ', Definition_, 1) > 0 THEN SUBSTRING( Definition_, 1, CHARINDEX(' ', Definition_, 1)-1 ) ELSE Definition_ END
,LastName = CASE WHEN CHARINDEX(' ', Definition_, 1) > 0 THEN SUBSTRING( Definition_, CHARINDEX(' ', Definition_, 1)+1, LEN(Definition_) ) ELSE '' END
SELECT Definition_
, Name
, LastName
FROM #LG_XX_
March 22, 2013 at 5:43 am
Magnificent Terry !
Very very thanks,
Best Regards
SELECT
STL.LOGICALREF,
STL.DATE_ AS TARIH,
--CC.DEFINITION_ AS ADI_SOYADI,
CASE WHEN CHARINDEX(' ', CC.DEFINITION_, 1) > 0 THEN SUBSTRING( CC.DEFINITION_, 1, CHARINDEX(' ', CC.DEFINITION_, 1)-1 ) ELSE CC.DEFINITION_ END AS ADI,
CASE WHEN CHARINDEX(' ', CC.DEFINITION_, 1) > 0 THEN SUBSTRING( CC.DEFINITION_, CHARINDEX(' ', CC.DEFINITION_, 1)+1, LEN(CC.DEFINITION_) ) ELSE '' END AS SOYADI,
CC.TCKNO AS TC_Kimlik,
CC.TAXNR AS Vergi_Kimlik,
STOK.CODE AS 'Stok Kodu',
STL.VAT AS KDV_ORAN,
STL.VATMATRAH AS Vergiye_Tabii_Matrah,
(STL.VATMATRAH*((STL.VAT/100)+1))-STL.VATMATRAH AS 'Toplam Vergi',
STL.VATAMNT AS Tevkif_Edilen_KDV_Tutari,
((STL.VATMATRAH*((STL.VAT/100)+1))-STL.VATMATRAH)-STL.VATAMNT AS 'Beyan Edilen KDV',
CASE (STL.CANDEDUCT)WHEN 1 THEN 'TEVKIFATLI'
WHEN 2 THEN 'TEVKIFATSIZ'
ELSE 'DIGER'
END AS Tevkifat,
CONVERT(NVarchar,FTR.DEDUCTIONPART1)+'/'+CONVERT(NVarchar,FTR.DEDUCTIONPART2) AS 'Tevkifat Orani'
FROM
LG_011_01_STLINE AS STL JOIN LG_011_CLCARD AS CC ON CC.LOGICALREF=STL.CLIENTREF
JOIN LG_011_ITEMS AS STOK ON STOK.LOGICALREF=STL.STOCKREF JOIN LG_011_01_INVOICE AS FTR
ON FTR.LOGICALREF=STL.INVOICEREF WHERE STOK.CODE LIKE '%770.%' OR STOK.CODE LIKE '653.%' OR STOK.CODE LIKE '740.%'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply