Skip to main content

Featured

[XrmToolBox] Use FetchXML Builder

FetchXML Installation : XrmToolBox 1. Open XrmToolBox And Find FetchXML Builder 2. Start FetchXML Builder 3. Click the fetch from node 사용은 Top 또는 Paging 둘 중 하나만 가능 Paging size : 한 페이지에 보여줄 레코드 개수 Dstinct : 페이지에 보여지는 레코드 중복 제거 No-lock : 테이블 lock 거는 것을 풀어줌 (조회 할 때 퍼블리시되지 않은 값도 들어올 수 있음) Page : 페이지 (미입력시 1 페이지) 4. Click entity form node 조회할 Entity Name을 선택 후, Select Attributes 클릭 조회할 Entity의 Attirbute를 선택하여 확인 link-entity 클릭 Relationship을 클릭하여 링크할 관계 선택 후 결과
Post a Comment
Read more

[Reference] How to replace non numeric string to numeric string

From Here

Prajesh says "a function for you in TSQL doing exactly what you want. It takes 13 secs if you dont use the function for 100,000 records  and 8 secs if you are using functions for same records, so it seems pretty fast."

 
SELECT Data
FROM TestFunctionPerf --13 sec

SELECT dbo.fnGetNumericOnly(Data) AS Data
FROM TestFunctionPerf -- 8 sec
CREATE FUNCTION fnGetNumericOnly (@string VARCHAR(500))
RETURNS VARCHAR(500)
AS
BEGIN
 DECLARE @NumericOnlyPart VARCHAR(500) = '';
 DECLARE @Numeric VARCHAR(1) = ''
 DECLARE @start INT = 1;
 DECLARE @end INT = 1

 SELECT @end = LEN(@string);

 WHILE (@start <= @end)
 BEGIN
  SET @Numeric = SUBSTRING(@string, @start, @start + 1)

  IF ASCII(@Numeric) >= 48
   AND ASCII(@Numeric) <= 57
  BEGIN
   SET @NumericOnlyPart = @NumericOnlyPart + @Numeric;
  END

  SET @start = @start + 1;
 END

 RETURN @NumericOnlyPart
END
GO

--Test Basic
SELECT dbo.fnGetNumericOnly('12 fgfgf.,jhkjh  khk 56789jhjh67')
GO

--Test on 100,000 Records
CREATE TABLE TestFunctionPerf (Data VARCHAR(8000))
GO

TRUNCATE TABLE TestFunctionPerf
GO

DECLARE @start INT = 1;
DECLARE @end INT = 100000 -- 100,000 Records

WHILE (@start <= @end)
BEGIN
 INSERT INTO TestFunctionPerf
 VALUES ('12 fgfgf.,jhkjh  khk 56789jhjh67')

 SET @start = @start + 1
END
GO

SELECT Data
FROM TestFunctionPerf --13 sec

SELECT dbo.fnGetNumericOnly(Data) AS Data
FROM TestFunctionPerf -- 8 sec

Comments

Post a Comment